The ASM script of all ASM scripts !

The asm information script I use which gives me everything I think I need in one go.

If there are any queries that others find useful please comment on them and I will add them to the script.

 

Credit where credit is due. I think Alan Cooper wrote the original version, although it has been amended since then.

 

set wrap off

set lines 120

set pages 999

col “Group Name”   form a25

col “Disk Name”    form a30

col “State”  form a15

col “Type”   form a7

col “Free GB”   form 9,999

 

prompt

prompt ASM Disk Groups

prompt ===============

select group_number  “Group”

,      name          “Group Name”

,      state         “State”

,      type          “Type”

,      total_mb/1024 “Total GB”

,      free_mb/1024  “Free GB”

from   v$asm_diskgroup

/

 

prompt

prompt ASM Disks

prompt =========

 

col “Group”          form 999

col “Disk”           form 999

col “Header”         form a9

col “Mode”           form a8

col “Redundancy”     form a10

col “Failure Group”  form a10

col “Path”           form a19

 

select group_number  “Group”

,      disk_number   “Disk”

,      header_status “Header”

,      mode_status   “Mode”

,      state         “State”

,      redundancy    “Redundancy”

,      total_mb      “Total MB”

,      free_mb       “Free MB”

,      name          “Disk Name”

,      failgroup     “Failure Group”

,      path          “Path”

from   v$asm_disk

order by group_number

,        disk_number

/

 

prompt

prompt Instances currently accessing these diskgroups

prompt ==============================================

col “Instance” form a8

select c.group_number  “Group”

,      g.name          “Group Name”

,      c.instance_name “Instance”

from   v$asm_client c

,      v$asm_diskgroup g

where  g.group_number=c.group_number

/

 

prompt

prompt Current ASM disk operations

prompt ===========================

select *

from   v$asm_operation

/

 

prompt

prompt free ASM disks and their paths

prompt ===========================

select header_status , mode_status, path from V$asm_disk

where header_status in (‘FORMER’,'CANDIDATE’)

/

 

clear columns

Script to tidy archivelogs from ASM and RMAN

We have a pre-production RAC cluster that is kept in archivelog mode to allow true performance monitoring (and to be used for Streams and DataGuard testing). However we do not need the archivelogs for recovery purposes and as we perform high-volume testing the +FRA diskgroup (on solid-state disk) gets full very quickly.

I wrote a script that can be run to quickly free up space. It connects to the ASM instance and removes the logfiles.

It sets the SID and ORACLE_HOME to that of the RAC instance then runs RMAN to perform a crosscheck and delete of the archivelogs.

Not particularly complex but efficient.

export ORACLE_SID=+ASM1
export ORACLE_HOME=/u00/app/asm/product/11.1.0/db_1

asmcmd -p << EOF

ls FRA/RACCLUSTER/ARCHIVELOG/*2008*/*
rm -rf FRA/RACCLUSTER/ARCHIVELOG/*
ls FRA/RACCLUSTER/ARCHIVELOG/*
EOF

export ORACLE_SID=RACSID
export ORACLE_HOME=/u00/app/oracle/product/11.1.0/db_2

rman target / catalog username/password@catdb << EOF1

CHANGE ARCHIVELOG ALL VALIDATE;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
EOF1

RMAN backup script – example – logging output

To answer a question about writing the output from RMAN commands to a logfile I posted a copy of a shell script I use to the Oracle-L newsgroup. It is a script I use for testing which does explain the multiple RMAN commands, much of which are commented out.

I

#!/bin/ksh

export ORACLE_HOME=/u00/app/oracle/product/11.1.0/db_2

export ORACLE_SID=TEST1

export PATH=$PATH:$ORACLE_HOME/bin

export NLS_DATE_FORMAT=’DD-MON-YY HH24:MI:SS’

export DATE=$(date +%Y-%m-%d)

rman target backup_admin/xxxxxxx catalog rman/xxxxxxx@server msglog /export/backups/rman/11g/logs/rman_full_backup_db_online_TEST1_${DATE}.log <<EOF

#backup AS COMPRESSED BACKUPSET database ;

#backup AS COMPRESSED BACKUPSET archivelog all delete input ;

backup AS COMPRESSED BACKUPSET database plus archivelog delete input;

#backup AS COMPRESSED BACKUPSET database;

#backup database;

#backup archivelog all delete input;

#BACKUP INCREMENTAL LEVEL 0 TAG = WEEKLY DATABASE;

#delete noprompt force obsolete;

#Change archivelog all validate

exit;

EOF

exit

 

From the Oracle documentation I also found the following notes

Using RMAN with Command Files
A command file is a text file which contains RMAN commands as you would enter them at the command line. You can run the a command file by specifying its name on the command line. The contents of the command file will be interpreted as if entered at the command line. If the LOG command line argument is specified, RMAN directs output to the named log file. Command files are one way to automate scheduled backups through an operating system job control facility.

In this example, a sample RMAN script is placed into a command file called commandfile.rcv. You can run this file from the operating system command line and write the output into the log file outfile.txt as follows:

% rman TARGET / CATALOG rman/cat@catdb CMDFILE commandfile.rcv LOG outfile.txt
Directing RMAN Output to a Log File
When you run RMAN in command line mode, it sends the output to the terminal. If you specify the LOG option, then RMAN writes the output to a specified log file instead.

Output for currently executing RMAN jobs is also stored in the V$RMAN_OUTPUT view, which reads only from memory (that is, the information is not stored in the control file). The V$RMAN_STATUS view contains metadata about jobs in progress as well as completed jobs. The metadata for completed jobs is stored in the control file.