Automating Database Startup and Shutdown

Automating Database Startup and Shutdown :-

Whenever you reboot your linux machine, do you have to start your database and listener manually everytime ? Then in that case , you can create linux service for it and that will take care of all the headache of starting / shutting it down .

To automate database startup and shutdown , we will use the dbstart and dbshut scripts located in $ORACLE_HOME/bin directory. First we will look at few basic Linux terms used in automating and then the actual process .

Version Figuring :-

My Linux Version :-

bash-3.00# cat /proc/version 
Linux version 2.6.9-89.0.0.0.1.ELxenU (mockbuild@ca-build10.us.oracle.com) (gcc version 3.4.6 20060404 (Red Hat 3.4.6-11.0.1)) #1 SMP Tue May 19 04:48:26 EDT 2009

Database Version :-

SQL> select version from v$instance;

VERSION
-----------------
11.2.0.1.0

Some Basics :-

1. Startup of Linux :-

When you boot your computer, the first thing that it will do is load the bootloader -- either GRUB or LILO in most cases. The bootloader will then load the Linux kernel -- the core operating system. Next, a process called init starts. This process reads the file /etc/inittab to establish the runlevel to use. The runlevel is the start mode for the computer.

Once init knows the runlevel it will look for the appropriate files or directories as defined in /etc/initab. Init will then either run the script files defined by /etc/initab, or run the script files in the directories defined by /etc/initab (depending on the set up of your system).

Finally, init will present you with the logon mode that you've selected.

2. Run Level (LINUX)

At the first thought,this might look like some different levels that the system goes through during a boot up. Instead, runlevel is the point at which the system is entered. Runlevel 1 is the most basic configuration (simple single user access using an text interface), while runlevel 5 is the most advanced (multi-user, networking, and a GUI front end). Runlevels 0 and 6 are used for halting and rebooting the system. Rest depends on the different distributions of Linux .

# Default runlevel. The runlevels used by RHS are:
# 0 - halt (Do NOT set initdefault to this)
# 1 - Single user mode
# 2 - Multiuser, without NFS (The same as 3, if you do not have networking)
# 3 - Full multiuser mode
# 4 - unused
# 5 - X11
# 6 - reboot (Do NOT set initdefault to this)

Each runlevel has its own set of start(S) and kill(K) scripts but all these scripts are supported in the directory /etc/rc.d/init.d or /etc/init.d. This is because the start and kill scripts are soft links to the files in the /etc/rc.d/init.d or /etc/init.d directory.

3. INITTAB file

The default path of the file is "/etc/inittab" and this file describes how the INIT process should set up the system in a certain run-level and the processes to be run at each runlevel.

An entry in the inittab file has the following format:

id:runlevels:action:process

* id – A unique sequence of 1-4 characters which identifies an entry in inittab.
* runlevels – Lists the runlevels for which the specified action should be taken. This field may contain multiple characters for different runlevels allowing a particular process to run at multiple runlevels. For example, 123 specifies that the process should be started in runlevels 1, 2, and 3.
* action - Describes which action should be taken.
* process – Specifies the process to be executed.


Example :- vim /etc/inittab and you can see these type of entries ,

id:3:initdefault:

The id is "id" which stands for initdefault. Note it is unique on all the numbered lines. The runlevel is 3 which sets the default starting runlevel to runlevel 3. The action is initdefault which tells init to make this runlevel the default runlevel. Note that the process field is blank since it is ignored by the initdefault action.

l0:0:wait:/etc/rc.d/rc 0
l1:1:wait:/etc/rc.d/rc 1
l2:2:wait:/etc/rc.d/rc 2
l3:3:wait:/etc/rc.d/rc 3
l4:4:wait:/etc/rc.d/rc 4
l5:5:wait:/etc/rc.d/rc 5
l6:6:wait:/etc/rc.d/rc 6

These lines tell init to run the program "/etc/rc.d/rc" for runlevels 0 through 6. Note that for each line the appropriate runlevel is passed to the "/etc/rc.d/rc" script program on the command line. For example note on line 5 above the second field is the runlevel specifying 2. At the end of the line there is a space and a 2 which allows the variable 2 to be passed on the command line to the program.

Automating Process :-

1. Log in as root user in your database server .

bash-3.00# id
uid=0(root) gid=0(root) groups=8500(dba),8501(oper),34234(aimegrp),42424(svrtech)

2. ORATAB File :-

"oratab" is a file created by Oracle in the /etc or /var/opt/oracle directory when installing database software. Originally ORATAB was used for SQL*Net V1, but now it's being used to list the databases and software versions installed on a server.

Entries are of the form:
$ORACLE_SID:$ORACLE_HOME::

The first and second fields are the system identifier and home directory of the database respectively.The third filed(Y or N) is used by Oracle's "dbstart" and "dbshut" scripts to figure out which instances are to be start up or shut down.

Now we have one database installed on my machine with ORACLE_SID=orcl and we will edit the third field of it to "Y".

bash-3.00# vim /etc/oratab

orcl:/scratch/aime1/app/aime1/product/11.2.0/dbhome_1:Y

3. Now go to /etc/init.d folder in Linux

( the directory is different for HP-UX and AIX)

bash-3.00# cd /etc/init.d

4. Create a file called dbora and copy the following lines :-

NOTE:-
Change the value of the ORACLE_HOME environment variable to an Oracle home directory for the installation. Change the value of the ORACLE environment variable to the user name of the owner of the database installed in the Oracle home directory

#! /bin/sh  -x
#
# Change the value of ORACLE_HOME to specify the correct Oracle home directory for your installation.

ORACLE_HOME=/scratch/aime1/app/aime1/product/11.2.0/dbhome_1

# Change the value of ORACLE to the login name of the oracle owner at your site.

ORACLE=aime1

PATH=${PATH}:$ORACLE_HOME/bin
HOST=`hostname`
PLATFORM=`uname`
export ORACLE_HOME PATH
#
if [ ! "$2" = "ORA_DB" ] ; then
   if [ "$PLATFORM" = "HP-UX" ] ; then
      remsh $HOST -l $ORACLE -n "$0 $1 ORA_DB"
      exit
   else
      rsh $HOST -l $ORACLE  $0 $1 ORA_DB
      if [ "$PLATFORM" = "Linux" ] ; then
         touch /var/lock/subsys/dbora
      fi
      exit
   fi
fi
#
case $1 in
'start')
        $ORACLE_HOME/bin/dbstart $ORACLE_HOME &
        ;;
'stop')
        $ORACLE_HOME/bin/dbshut $ORACLE_HOME &
        ;;
*)
        echo "usage: $0 {start|stop}"
        exit
        ;;
esac
#
exit

The touch command in the script :-
touch /var/lock/subsys/dbora

Creates a file known as "/var/lock/subsys/dbora", if the file does not already exist. If the file already exists the accessed / modification time is updated for the file.

Also, every script can be executed as both a start and a stop script, these scripts must understand the parameters start and stop. The scripts understand, in addition, the restart, reload, force-reload, and status options.

NOTE:-

If the listener name is not the default name, LISTENER, then you must specify the listener name in the stop and start commands:
$ORACLE_HOME/bin/lsnrctl {start|stop} listener_name

5. Change the group and file permissions of the file "dbora"

bash-3.00# ls -l dbora
-rw-r--r--  1 root root 879 Jan 18 01:46 dbora
bash-3.00# chgrp dba dbora
bash-3.00# chmod 750 dbora
bash-3.00# ls -l dbora
-rwxr-x---  1 root dba 879 Jan 18 01:46 dbora

6. Now the last step is to associate the dbora service with the appropriate run levels .

There are two methods to do it :-
6.1 Manually by creating symbolic links
6.2 Using chkconfig

6.1 Creating symbolic links :-

In order to have a script execute for a certain run level, all that's needed is to create a symlink in that run level's 'rc' directory
to the original script file. Hence ,we will create symbolic links of the dbora script in the rc.d folder in the appropriate run level folder.

Here we are creating two type of symbolic link , one for shutdown(Kill) and another for startup .

The first one creates a "K01dbora" link in run level 0 "Halt". K stands for kill and next two number tells us about the shutdown priority . Here we need to shtudown the dbora service before any other service shuts down .

bash-3.00# ln -s /etc/init.d/dbora /etc/rc.d/rc0.d/K01dbora

These two creates "S99dbora" link in run level 3 and 5 . S stands for startup and 99 is the startup priority i.e. start after all services are started.

bash-3.00# ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
bash-3.00# ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora

6.2 Using chkconfig :-

We can run a couple of commands to see if you have it installed.
For example:

# rpm -qa |grep chkconfig
chkconfig-1.3.11.1-1

# whereis chkconfig
chkconfig: /sbin/chkconfig /usr/share/man/man8/chkconfig.8.gz

chkconfig provides a simple command-line tool for maintaining the /etc/rc[0-6].d directory hierarchy by relieving system administrators of the task of directly manipulating the numerous symbolic links in those directories.

chkconfig --add name

This option adds a new service for management by chkconfig. When a new service is added, chkconfig ensures that the service has either a start or a kill entry in every runlevel. If any runlevel is missing such an entry, chkconfig creates the appropriate entry as specified by the default values in the init script. Note that default entries in LSB-delimited 'INIT INFO' sections take precedence over the default runlevels in the initscript.

Each service which should be manageable by chkconfig needs two or more commented lines added to its init.d script. The first line tells chkconfig what runlevels the service should be started in by default, as well as the start and stop priority levels. If the service should not, by default, be started in any runlevels, a - should be used in place of the runlevels list. The second line contains a description for the service, and may be extended across multiple lines with backslash continuation.

For example :-
dbora should contain :-

# chkconfig: 35 99 01
# description: Oracle auto start stop script \

For more info on chkconfig :-
http://linux.about.com/library/cmd/blcmdl8_chkconfig.htm

Error Tracking :-

In case of Red Hat Lniux , if your service is not started , you can check the error messages at "/var/log/messages"

Suppose in /var/log/messages , you get error

Jan 18 11:45:44 adc2110341 in.rshd[4969]: rsh denied to root@adc2110341.us.oracle.com as aime1: Permission denied.

Then you need to verify two things :-

1. Verify if rsh is enabled for localhost to work without supplying a password
2. If root has access to rsh localhost as aime1 .

For this you need to check different files :-
1. /etc/securetty
Make sure rsh and rlogin have their entries

2. /root/.rhosts have entries :-
adc2110341 aime1
localhost aime1
adc2110341 root
localhost root

3. /etc/hosts.equiv also has its corresponding entry .

If /var/log/messages doesn't show any error but your database is not started , then it shows the location of listener.log and startup.log file , you can check the error inside that. In that case , error might be the wrong Oracle user supplied for startup .

RECYCLE BIN IN DATABASE

Introduction: -

We always hear the word 'Recycle Bin' in Windows but I never knew that such a word exists in database too till the time I first read it .My first question was has this something to do with Windows Recycle Bin! But the answer is no.

Recycle Bin in database is actually a data dictionary table, which contains information about the dropped objects. So if you have ever dropped a table by mistake and wished you could undo that, recycle bin is to your rescue. When you drop a table, the database does not immediately remove the space associated with the table but instead it renames the table and places it and any associated objects in a recycle bin.

We can enable or disable the recycle bin using the initialization parameter recyclebin.

 

SQL> show parameter bin;



NAME                                 TYPE        VALUE

------------------------------------ ----------- -------------------

recyclebin                           string      on

We can set/reset it for a particular session/system by using: -

Alter system set recyclebin=on/off
Alter session set recyclebin=on/off


Using Recycle Bin: -

A user can view his objects in the recycle bin by: -

 

SQL> select * from recyclebin;

No rows selected

Currently, the recyclebin for the user is empty.


Now lets create a table: -

 

SQL> create table test (a number);



Table created.



SQL> select * from recyclebin;



no rows selected

Now lets drop the table, which we created just now: -

 

SQL> drop table test;



Table dropped.

Lets peep into the recycle bin now,

 

SQL> select object_name, original_name, operation from recyclebin;



OBJECT_NAME                                   ORIGINAL_NAME               OPERATION

------------------------------ -------------------------------- ---------

BIN$VLKNkYA8S/arE/fy6G6LCg==$0      TEST                             DROP

When a dropped table is moved to the recycle bin, the table and its associated objects are given system-generated names so as to avoid name conflicts that may arise if multiple tables have the same name. Like if User A drops a table, re-creates it with the same name, then drops it again, there will be naming conflicts in the recyclebin or if two users have tables with the same name, and both users drop their tables.
The renaming convention, which the recyclebin follows, is: -
BIN$unique_id$version
Where:
Unique_id is a 26-character globally unique identifier for this object, which makes the recycle bin name unique across all databases
Version is a version number assigned by the database


We can query the object in the recycle bin in the same way we query other similar objects.

 

	SQL> select * from BIN$VLKNkYA8S/arE/fy6G6LCg==$0;

	No rows selected.

Removing from RecycleBin: -

This actually deals with two cases: -
1. We want to remove what’s their in recyclebin
2. We don’t want some particular tables to go to recycle bin, instead directly deleted

In the first case, we can use the PURGE command to remove the entries from the recyclebin. If we want to remove a particular table and all its associated entries, we can use: -

 

SQL> purge table test;



Table purged.



SQL> show recyclebin;

Here in purge table, we can specify the original name of the table or system generated names; the effect remains same for both the commands.

If we want to delete all the entries belonging to particular tablespace; we can use: -

 

	SQL> purge tablespace <>;

And if we want to empty the complete recyclebin,

 	

	SQL> purge recyclebin;

You cannot roll back a PURGE statement, nor can you recover an object after it is purged


In other case, while dropping the table; we can use the purge clause so that it is directly deleted and space is immediately free.

 

SQL> show recyclebin;



SQL> drop table test purge;

Table dropped.



SQL> show recyclebin;

Restoring from Recyclebin: -

To reinstate the table, all we have to do is use the “Flashback Table” command.

 

SQL>flashback table test to before drop rename to test;

Flashback complete.



SQL> select * from test;

         A

----------

         1

         2



SQL> show recyclebin;

We can see the table test is back into the original state where it was before drop. But if the table has the indexes built on it, what happens to them? Lets see…

 

SQL> create table test(a number primary key);

Table created.

SQL> insert into test values(1);

1 row created.

SQL> insert into test values(2);

1 row created.

SQL> drop table test;

Table dropped.



SQL> select object_name,original_name,type from recyclebin;



OBJECT_NAME                    ORIGINAL_NAME

------------------------------ --------------------------------

TYPE

-------------------------

BIN$pjJQ48mbQl297TtOUMII6w==$0 SYS_C0016363

INDEX



BIN$a8rd/wnOQZexb+NOndiqjw==$0 TEST

TABLE



SQL> flashback table test to before drop rename to test;

Flashback complete.



SQL> select object_name,object_type from user_objects;



OBJECT_NAME                    OBJECT_TYPE

------------------------------ -------------------

PK_EMP                                		   INDEX

DEPT                             			TABLE

PK_DEPT                      			  INDEX

EMP                            			TABLE

BONUS                        			  TABLE

SALGRADE                  		   	  TABLE

TEST                          		    	 TABLE

BIN$pjJQ48mbQl297TtOUMII6w==$0 INDEX



8 rows selected.

The index still has the system-generated name assigned to it. If you want to get the original name back, you need to do it manually using

 

SQL> ALTER INDEX "BIN$pjJQ48mbQl297TtOUMII6w==$0" RENAME TO test_pk;



Index altered.



SQL> select object_name,object_type from user_objects;



OBJECT_NAME                    OBJECT_TYPE

------------------------------ -------------------

PK_EMP                         INDEX

DEPT                           TABLE

PK_DEPT                        INDEX

EMP                            TABLE

BONUS                          TABLE

SALGRADE                       TABLE

TEST                           TABLE

TEST_PK                        INDEX



8 rows selected.

Recyclebin also supports multiple versions of the same table so that we can get back to any older version: -

 

SQL> CREATE TABLE TEST (a NUMBER);

Table created.

SQL> INSERT INTO TEST VALUES (1);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> DROP TABLE TEST;

Table dropped.



SQL> CREATE TABLE TEST (a NUMBER);

Table created.

SQL> INSERT INTO TEST VALUES (2);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> DROP TABLE TEST;

Table dropped.



SQL> CREATE TABLE TEST (a NUMBER);

Table created.

SQL> INSERT INTO TEST VALUES (3);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> DROP TABLE TEST;

Table dropped.



SQL> show recyclebin;



ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

---------------- ------------------------------ ------------ -------------------



TEST             BIN$JLFMRW9VTRCtxREKNeyDlg==$0 TABLE        2010-04-02:15:07:16



TEST             BIN$nydz2eBWTM+UJKbCyfKGzw==$0 TABLE        2010-04-02:15:07:16



TEST             BIN$sIjIxu4HRVu4gTp/KTKCkg==$0 TABLE        2010-04-02:15:07:16

Now we have all the three versions of test and we can restore to any table using our flashback command and using the recyclebin name.

 

SQL> Flashback table "BIN$sIjIxu4HRVu4gTp/KTKCkg==$0" to before drop rename to test3;



Flashback complete.



SQL> select * from test3;



      COL1

----------

         2