Oracle Documentation Library

Below page contains documents of all the versions of Oracle Database:

http://www.oracle.com/technology/documentation/index.html

--

Basic Grid Control commands

[oracle@naikh]export OMS_HOME=/u01/oracle/product/oms10g
[oracle@naikh]export AGENT_HOME=/u01/oracle/product/agent10g

Starting Grid Control Services

$AGENT_HOME/bin/emctl start agent
$OMS_HOME/opmn/bin/opmnctl startall


Stopping Grid Control Services

$AGENT_HOME/bin/emctl stop agent
$OMS_HOME/opmn/bin/opmnctl stoptall


Status of the Grid Control Services

$AGENT_HOME/bin/emctl status agent
$OMS_HOME/bin/emctl status oms
$OMS_HOME/opmn/bin/opmnctl status

Rebooting RAC servers

For some maintenance activity, if RAC servers need to be rebooted then the steps are:

1. Check the status of all the services
[oracle@naikh] cd $CRS_HOME/bin
[oracle@naikh] crs_stat -t

2. Stop the database
[oracle@naikh] srvctl stop database -d orcl

3. Stop nodeapps
[oracle@naikh] srvctl stop nodeapps hostname1
[oracle@naikh] srvctl stop nodeapps hostname2

4. Reboot the servers
Ask System admin to reboot the both the servers

5. Once the server is up check the status of all the services
[oracle@naikh] cd $CRS_HOME/bin
[oracle@naikh] crs_stat -t

6. Start the nodeapps if not automatically started.
[oracle@naikh] srvctl start nodeapps hostname1
[oracle@naikh] srvctl start nodeapps hostname2

7. Start the database if not automatically started.
[oracle@naikh] srvctl start database -d orcl

8. Make sure all the services are active
[oracle@naikh] cd $CRS_HOME/bin
[oracle@naikh] crs_stat -t

RMAN Recovery Catalog

This article gives the steps to create the recovery catalog.

1. Identify the database where catalog needs to be created.

Here I am using rcvcat as the catalog database

2. Create the tablespace to store the catalog information.

SQL> CREATE TABLESPACE CATALOG_DATA
DATAFILE '/u01/oradata/rvcvat/catalog_data_01.dbf'
SIZE 500M;

3. Create the user that is going to be the catalog owner

SQL> CREATE USER RMAN
IDENTIFIED BY RMAN
DEFAULT TABLESPACE CATALOG_DATA
TEMPORARY TABLESPACE TEMP;

4. Grant the roles and the privileges

SQL> GRANT RESOURCE TO RMAN;
SQL> GRANT CONNECT TO RMAN;

SQL> GRANT RECOVERY_CATALOG_OWNER TO RMAN;

SQL> ALTER USER RMAN DEFAULT ROLE ALL;
SQL> ALTER USER RMAN QUOTA UNLIMITED ON RMAN_DATA;

5. Create the catalog

[oracle@naikh]$ rman catalog rman/rman@rvccat
RMAN> create catalog;

6. Register the target database

[oracle@naikh]$ export ORACLE_SID=orcl

[oracle@naikh]$ rman target / catalog rman/rman@rvccat

RMAN> register database;

Applying RMAN Incremental Backup To Standby Database

We can use RMAN incremental backup to roll forward the physical standby database in the following situations:
1. when an archive sequence is missing
2. when lots of archives needs to be applied

Steps:

1. Take the current SCN of the standby database

SQL> select to_char(current_scn) scn from v$database;
SCN
---------------
23339995

2. Stop the redo apply on the standby database

SQL> alter database recover managed standby database cancel;

3. Take the incremental backup of the production database from the current SCN of the standby database

RMAN> run {
allocate channel c1 type disk format '/backup/%d_incr_s%s_p%p' maxpiecesize 2g;
allocate channel c2 type disk format '/backup/%d_incr_s%s_p%p' maxpiecesize 2g;
allocate channel c3 type disk format '/backup/%d_incr_s%s_p%p' maxpiecesize 2g;
allocate channel c4 type disk format '/backup/%d_incr_s%s_p%p' maxpiecesize 2g;
backup as compressed backupset incremental from scn 23339995 database;
}

4. Transfer the incremental backup to the standby system

$ scp /backup/* usename@standbyhost:/tmp/

5. Catalog all the incremental backup pieces to the standby database

RMAN> catalog start with '/tmp/' ;

6. Apply incremental backup to the standby database

RMAN> recover database noredo;

7. Create new standby controlfile at the production database, copy this to standby system and replace current standby controlfile with this new one.

SQL> alter database create standby controlfile as '/backup/standby_incr.ctl';

8. Start redo apply on the physical standby database

SQL> alter database recover managed standby database disconnect;


Note:
If you have added new datafile to the production and if it is not at created at standby you have to restore the datafile.
RAMN> restore datafile 56,57,58;

Password Expiration Notification

This is a simple script to notify about password expiration to users.

#!/bin/bash

# Set the environment variables
export ORACLE_HOME=/opt/oracle/product/102/DB
export ORALCE_SID=oradb
export PATH=$PATH:$ORACLE_HOME/bin

# Declar the vaiable
NOTIFY_LIST=user_name@abc.com
DIFF_FILE=/tmp/pass_exp.log

# Get expiry date
EXP_DATE=`chage -l oracle grep "Password Expires" awk '{print $4"-"$3"-"$5}'sed 's/,//'`

# Calculte in how many days password will expire
sqlplus usr/pass@$ORACLE_SID<<>
--CREATE TABLE pwd_expire (expire_date date);
INSERT INTO pwd_expire values (TO_DATE('$EXP_DATE','DD-MON-YYYY'));
COMMIT;
SET ECHO OFF FEEDBACK OFF
SPOOL $DIFF_FILE
SELECT ROUND(expire_date-sysdate) diff FROM pwd_expire;
SPOOL OFF
--DROP TABLE pwd_expire;
TRUNCATE TABLE pwd_expire;
EXIT

EOF

DAYS=`tail -2 $DIFF_FILEhead -1awk '{print $1}'`


if [ $DAYS -le 5 ]
then
mailx -s "Oracle Password will expires in $DAYS" $NOTIFY_LIST < /dev/null
fi

AXEL: Download accelerator for Linux

AXEL is a download accelerator for Linux. This utility also can be used to transfer files from one machine to another.

Download:
axel -n 16 -a http://download.com/download_file.gz

File Transfer:
axel -n 16 -a ftp://username/password@remote_host//temp/expdp_naikh_01.dmp

axel -n 16 -a ftp://username/password@remote_host//temp/expdp_naikh*

Applying Oracle Patchset

Aplying patchset p5337014_10203_LINUX.zip to upgrade from 10201 to 10203.

1. Shut down the database
SQL> shut immediate

2. Stop the listener
$ lsnrctl stop

3. Unzip the patchset and run OUI
$ ./runinstaller

4. Start the listener
$ lsnrctl start

5. Run the following commnads
SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> SPOOL OFF

6. Restart the database
SQL> SHUT IMMEDIATE
SQL> STARTUP

7. Run the utlrp.sql script to recompile all invalid PL/SQL packages
SQL> @?/rdbms/admin/utlrp.sql

Bash Shell Scripting Guide

1. Basics
a. The first line of the script must be
#!/bin/bash
b. Make the script exicutable
chmod +x test.sh

2. Conditional Statement
2.1. if statement
syntax:
if [ condition ]
then
commands
fi

2.2. if else statement
syntax:
if [ condition ]
then
commands
else
commands
fi

2.3. if elif statement
syntax:
if [ condition1 ]
then
commands
elif [ condition2]
then
commands
else
commands
fi

3.1 Relational operators
-eg Equal to
-lt Less than
-gt Greater than
-ge Greater than or equal to
-le Less than or equel to

3.2 File Related Tests
-f file True if file exists and is a regular file
-r file True if file exists and is readable
-w file True if file exists and is writable
-x file True if file exists and is executable
-d file True if file exists and is a directory
-s file True if file exists and its size is greater than zero

3.3 String tests
-n str True if string str is not a null string
-z str True if string str is a null string
str1 == str2 True if both the strings are equel
str1 != str2 True if both the strings are not equel
str True if str is assigned a value and is not null

3.4 Multiple conditions
-a Performs the AND function
-o Performs the OR function

4. Case Statement
Syntax:
case expression in
pattert1) execute commands;;
pattert2) execute commands;;
pattert3) execute commands;;
....
esac

Example:
#!/bin/bash
case `datacut -d" " -f1` in
Mon) commands;;
Tue) commands;;
Wen) commands;;
....
esac

5. Looping Statements
5.1 while loop
syntax:
while [ condtion_is_true ]
do
execute commands
....
done

Example:
while [ $NUM -gt 100 ]
do
sleep
done

5.2 until loop
Syntax:
until [ condition_is_false ]
do
execute commands
done

Example:
until [ -f $FILE ]
do
sleep 5
done

5.3 for loop
Syntax:
for variable in list
do
execute commands
done

Example:
for I in 1 2 3 4 5
do
echo "The value of I is $I";
done

Example 2:
#!/bin/bash
LIMIT=10
for ((a=1;a<=$LIMIT;a++)) do echo "$a" done
6. Special symbols
$0 Name of the shell script being executed
$1 First parameter passed to the script
$* All the paramerts passes to the script
$# Number of parameters passed to the script
$? Exit status of the last command

7. Read statement
#!/bin/bash
echo "Enter your name:"
read NAME
echo "Hello $NAME, Have a nice day."

8. Functions
Syntax:
Function_name ()
{
statements
}

Example:
#!/bin/bash
sumcalc ()
{
SUM= $[ $1 + $2 ]
echo "Result = $SUM"
}

echo -e "Enter the first number:\c"
read NUM1
echo -e "Enter the second number:\c"
read NUM2
read NUM2

# Call the function
sumcal $NUM1 $NUM2

Standby Database Creation

Steps to create the physical standby database:
1. Take the backup of production database
2. Create standby controlfile
3. Copy the backup, standy controfile and init.ora files to standby box
4. Create necessary directories
5. Edit the init.ora file
6. Mount the database
7. Restore the database
8. Start the recovery

1. Take the backup of production database
rman> run {
allocate channel c1 type disk format '/backup/%d_rman_bkp_%T_p%p_s%s' maxpiecesize 1800M;
allocate channel c2 type disk format '/backup/%d_rman_bkp_%T_p%p_s%s' maxpiecesize 1800M;
allocate channel c3 type disk format '/backup/%d_rman_bkp_%T_p%p_s%s' maxpiecesize 1800M;
allocate channel c4 type disk format '/backup/%d_rman_bkp_%T_p%p_s%s' maxpiecesize 1800M;
backup as compressed backupset databse plus archivelog;
}

2. Create standby controlfile
sql> alter database create controlfile as '/backup/standby_controlfile.ctl';

3. Copy the backup, controfile and init.ora files to standby box
use ftp or scp to copy the files

4. Create necessary directories
$ mkdir adump bdump cdump udump datafiles controlfiles redofiles archive

5. Edit the init.ora file
a. Change controfile path
b. Change the paths of all dump_dest parametes
c. Change the LOG_ARCHIVE_DEST path
d. Add below 3 parametes
DB_FILE_NAME_CONVERT='/old_path1/','/new_path1/' ,'/old_path2/','/new_path2/'
LOG_FILE_NAME_CONVERT='/old_path/','/new_path/'
STANDBY_FILE_MANAGEMENT='AUTO'

6. Mount the database
sql> startup mount pfile='init.ora'

7. Restore the database
rman> run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
restore database;
}

8. Start the recovery
sql> alter database recover manged standby database disconnect;