Category Archives: Articles

This pages contains all the articles of dba-scripts.com

Stop/Start all RAC databases at once

RAC

Introduction

Stopping all RAC databases running on an ORACLE_HOME at once and saving the state before shutting them down is really helpfull when you are doing patching on a server.

In my case I have databases running on some nodes and not the others, it’s getting complicated to keep track of which database is running where. With this method you will shutdown all databases at once and keep track of the databases running on that node before shutting them down.

Stopping all RAC databases of an ORACLE_HOME

This command will stop all databases running on the ORACLE_HOME specified and save the state before the shutdown in a state file.

Shell
1
srvctl stop home -oraclehome /opt/oracle/base/product/12.1.0/dbhome_1 -node node1 -statefile /tmp/node1_stop.dmp

Starting all RAC databases of an ORACLE_HOME

This command uses a state file to restart all databases previously running on a node.

Shell
1
srvctl start home -oraclehome /opt/oracle/base/product/12.1.0/dbhome_1 -node node1 -statefile /tmp/node1_stop.dmp

 

RMAN Backup script

RMAN

This is a sample backup script I used, it has already a lot of options. Feel free to make any modification you want. If you add some good enhancements, let me know I can put them here so everybody can profit from them.

RMAN Backup script

Shell
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
#!/bin/bash
##############################################################################
#                            RMAN BACKUPS
##############################################################################
#
# Description: This script create a RMAN backup based on parameter passed
#
##############################################################################
#
# Author: Cyrille MODIANO
#
# Version : 1.0 (2016-07-12)
# Version : 1.1 (2016-12-15) - Added compression
# Version : 1.2 (2017-01-18) - Added parallelism and backup of a list of databases
#
##############################################################################
 
usage () {
echo "Usage : SID BACKUP_TYPE COMPRESSION PARALLELISM
        SID : SID, comma separated list of databases or ALL for all databases (running)
        BACKUP_TYPE : INCR, FULL, COLD or ARCH
        COMPRESS : COMPRESS or NOCOMPRESS to compress or not the backup
        PARALLEL : defines the number of channel to use
 
        exemple backup full : rman_backup.sh db1 FULL COMPRESS 16
        exemple backup arch : rman_backup.sh db1 ARCH NOCOMPRESS 2
"
}
 
##Variables definition
BASEDIR=$(dirname "$0")
BACKUP_BASE=/Data_Domain/oracle/prod/
LOGDIR=${BASEDIR}/log
 
 
DEST_EMAIL=example@example.com
export NLS_DATE_FORMAT='dd/mm/yyyy hh24:mi:ss'
DATE=`date +"%Y%m%d_%H%M%S"`
PATH=$PATH:/usr/local/bin
 
# Create directorires if not exist
mkdir -p $BACKUP_BASE/
mkdir -p $LOGDIR
mkdir -p $BACKUP_BASE/autobackup
 
 
# Validating du number of parameters passed
if [ $# -lt 4 ]; then
    usage
    exit 1
fi
 
# Parameters provided
DB_LIST=$1
BACKUP_TYPE=$2
PARALLEL=$4
 
# Backup type validation
case $BACKUP_TYPE in
    FULL)
        LEVEL="incremental level 0"
    ;;
    INCR)
        LEVEL="incremental level 1"
    ;;
    ARCH)
        LEVEL=""
    ;;
    COLD)
        LEVEL=""
    ;;
    *)
        usage
    exit 1
    ;;
esac
 
# Compression validation
if [ $3 = 'COMPRESS' ]; then
    COMPRESS='AS COMPRESSED BACKUPSET'
else
    if [ $3 = 'NOCOMPRESS' ]; then
        COMPRESS=''
    else
        usage
        exit 1
    fi
fi
 
##backup function
function backup_database() {
    # Set Oracle Environment for database
    ORACLE_SID=$1
    ORAENV_ASK=NO
    . oraenv
 
    OUTPUT_SID=${ORACLE_SID}
    BACKUP_DIR=$BACKUP_BASE/${ORACLE_SID}
    LOGFILE=$LOGDIR/rman_backup_${ORACLE_SID}_${BACKUP_TYPE}_${DATE}.log
    
 
    # Controlfile backup directory
    CF_BACKUP="'$BACKUP_DIR/autobackup/cf_sp_%F'"
    FORMAT_DATA="format '${BACKUP_DIR}/data_%d_${BACKUP_TYPE}_bks%s_%T_%U.bck'"
    FORMAT_ARCHIVE="format '${BACKUP_DIR}/arch_%d_${BACKUP_TYPE}_bks%s_%T_%U.bck'"
 
    if [ $BACKUP_TYPE = 'COLD' ]; then
        sqlplus -s / as sysdba <<EOF
            shutdown immediate;
            startup mount;
            exit
EOF
        rman target /  << EOF >> $LOGFILE 2>&1
            CONFIGURE CONTROLFILE AUTOBACKUP ON;
            CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ${CF_BACKUP};
            CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM ${PARALLEL};
            run {
                backup ${COMPRESS} database $FORMAT_DATA;
                delete noprompt obsolete;
                }
            exit
EOF
 
        sqlplus -s / as sysdba <<EOF
            alter database open;
            exit
EOF
 
    else
        if [  $BACKUP_TYPE = 'ARCH' ]; then
            rman target /  << EOF >> $LOGFILE
                CONFIGURE CONTROLFILE AUTOBACKUP ON;
                CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ${CF_BACKUP};
                CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM ${PARALLEL};
                run {
                    backup ${COMPRESS} archivelog all $FORMAT_ARCHIVE delete input filesperset 10;
                    delete noprompt obsolete;
                    }
                exit
EOF
        else
            rman target /  << EOF >> $LOGFILE 2>&1
                CONFIGURE CONTROLFILE AUTOBACKUP ON;
                CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ${CF_BACKUP};
                CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM ${PARALLEL};
                run {
                    backup ${COMPRESS} archivelog all $FORMAT_ARCHIVE delete input filesperset 10;
                    backup ${COMPRESS} ${LEVEL} database $FORMAT_DATA include current controlfile;
                    backup ${COMPRESS} archivelog all $FORMAT_ARCHIVE delete input filesperset 10;
                    delete noprompt obsolete;
                    }
                exit
EOF
        fi
    fi
 
    # Validate Errors in the log.
    ERRORLIST=$(egrep "^RMAN-[0-9]*:|^ORA-[0-9]*:" $LOGFILE)
    ERRORLIST=$(echo $ERRORLIST)
    if [ -n "$ERRORLIST" ]
    then
        SUBJECT="$(date +%y)/$(date +%m)/$(date +%d) $(date +%H).$(date +%M).$(date +%S) - `hostname` - Backup Report ${OUTPUT_SID} - ERROR"
    else
        SUBJECT="$(date +%y)/$(date +%m)/$(date +%d) $(date +%H).$(date +%M).$(date +%S) - `hostname` - Backup Report ${OUTPUT_SID}"
    fi
    cat -v $LOGFILE | mail -s "$SUBJECT" "$DEST_EMAIL"
}
 
if [ $1 = 'ALL' ]; then
    for database in `ps -ef | grep pmon | egrep -v 'ASM|grep' | awk '{print $8}' | cut -d_ -f3`
    do
        backup_database $database
    done
else
    for database in $(echo $1 | sed "s/,/ /g")
    do
        backup_database $database
    done
fi