In the previous article we learned few linux commands. Now let's write few utility scripts that we will use frequently while building the data guard.

Let’s admit: How frequently you are going to have to connect to SQL prompt or ASM prompt? Countless. Why not make our lives easier by scripting these common tasks, right?

I put all my Oracle Scripts under a directory named: ora_scripts

SQLPlus Script

Open up a vi editor or an editor of your choice on the primary server.

[oracle@primary_server ora_scripts] > vi sqlplus.sh

Paste the content below in the editor.

#!/bin/bash
echo "Setting up Oracle environment"
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
echo "ORACLE_HOME set to: " $ORACLE_HOME
export ORACLE_SID=cssdev
echo "ORACLE_SID set to: " $ORACLE_SID
cd $ORACLE_HOME/bin
./sqlplus /nolog @/u01/ora_scripts/db_status.sql

Notice few things in the script. I am setting few environment variables. ORACLE_HOME is quite obviously the Oracle Home on the primary server. I am setting ORACLE_SID to "cssdev".

Next, I am changing my directory to $ORACLE_HOME/bin. Why? Because that is where the "sqlplus" executable file is.

Next I log into sqlplus and run "db_status.sql". What is db_status.sql? A simple file that tells me the name and mode of the database. Before we create that file let’s save what is in the editor.

While in the editor, press Esc on the keyboard and then :wq to save the file.

Now let’s look at db_status.sql file.

connect /as sysdba
select name, open_mode from v$database;

Script connects to the database as a sysdba and runs a simple SQL statement to show the name and open mode of the database.

Note: connection “/as sysdba” is allowed with OS authentication. Notice we are not specifying username or a password. Take a look at the Oracle docs on how to configure the OS authentication.

ASM Script

Now let’s write a script that will put us on ASM CLI. Open a vi editor.

[oracle@primary_server ora_scripts]  > vi asm.sh

Copy and paste following script into vi editor.

#!/bin/bash
echo "Setting up Oracle environment"
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid
echo "Grid ORACLE_HOME set to: " $ORACLE_HOME
export ORACLE_SID=+ASM
echo "ASM ORACLE_SID set to: " $ORACLE_SID
cd $ORACLE_HOME/bin
./asmcmd

Simple, just like previous script we set ORACLE_HOME and ORACLE_SID. Except in this script we set grid as our ORACLE_HOME and point ORACLE_SID to ASM instance. Which is +ASM.

Running this script will put us on ASM prompt.

RMAN Script

Next let’s write a script to connect to RMAN prompt. We will use primary database backup to create a data guard and even grandma Annie, knows that backups are generated using RMAN. Are you an awesome DBA? Then I am sure you have and you know how to configure RMAN, right?

Open the vi editor.

[oracle@primary_server ora_scripts]  > vi rman.sh

Copy and paste the script below into vi editor.

#!/bin/bash
echo "Setting up Oracle environment"
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
echo "ORACLE_HOME set to: " $ORACLE_HOME
export ORACLE_SID=cssdev
echo "ORACLE_SID set to: " $ORACLE_SID
cd $ORACLE_HOME/bin
./rman

Save contents of the vi editor. Just like other scripts we set ORACLE_HOME & ORACLE_SID. Next we execute rman from the bin directory. Running this script will put us on to RMAN prompt. Now of course once you are at the RMAN prompt run the command below to connect to the primary database.

RMAN > connect target

DGMGRL script

What is DGMGRL? If you have never configured data guard then you probably don't know what is DGMGRL. It is a utility bundled with Oracle to manage active data guard environment. For now just save this script. Later in the series you will learn how to use DGMGRL.

#!/bin/bash
echo "Setting up Oracle environment"
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
echo "ORACLE_HOME set to: " $ORACLE_HOME
export ORACLE_SID=cssdev
echo "ORACLE_SID set to: " $ORACLE_SID
cd $ORACLE_HOME/bin
./dgmgrl

Keep these scripts closer to your heart. We will be using them a lot. Easy so far? Well I hope so. In the next article we will prepare primary database for active data guard.

See you then.