Tuesday, January 18, 2011

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 .

Thursday, January 13, 2011

Password File Authentication - UNIX

After looking at all the possibilities of OS level authentication in the previous post , we will look the password file auth in this post. First we will look at few basic concepts and then go into mroe detail :-

remote_login_passwordfile :-


The init parameter remote_login_passwordfile specifies if a password file is used to authenticate the DBA or not. There are three values this parameter can take and the values are :-

1. NONE: Oracle Database behaves as if the password file does not exist and hence doesnt use password file authentication.

2. EXCLUSIVE: (The default) An EXCLUSIVE password file can be used with only one instance of one database. Only an EXCLUSIVE file can be modified. Using an EXCLUSIVE password file enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command.

3. SHARED: A SHARED password file can be used by multiple databases running on the same server, or multiple instances of an Oracle Real Application Clusters (RAC) database. A SHARED password file cannot be modified. This means that you cannot add users to a SHARED password file. Any attempt to do so or to change the password of SYS or other users with the SYSDBA or SYSOPER privileges generates an error. All users needing SYSDBA or SYSOPER system privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can change REMOTE_LOGIN_PASSWORDFILE to SHARED, and then share the file.
This option is useful if you are administering multiple databases or a RAC database.
SQL> show parameter pass

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

Password File :-

The default location for the password file is: $ORACLE_HOME/dbs/orapw$ORACLE_SID on Unix and %ORACLE_HOME%\database\PWD%ORACLE_SID%.ora on Windows. We can create a password file using the password file creation utility, ORAPWD .

ORAPWD FILE=filename [ENTRIES=numusers]
[FORCE={Y|N}] [IGNORECASE={Y|N}] [NOSYSDBA={Y|N}]

Example :-
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=welcome1 entries=1 force=y;

ENTRIES : This argument specifies the number of entries that you require the password file to accept. This number corresponds to the number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. The actual number of allowable entries can be higher than the number of users, because the ORAPWD utility continues to assign password entries until an operating system block is filled

PASSWORD : The password for the SYS user. If you use an ALTER USER statement to change the password for the SYS user after you connect to the database, then both the password stored in the data dictionary and the password stored in the password file are updated ( Only in case of EXCLUSIVE access). This parameter is mandatory.

Connecting to SQLPLUS :-

First of all , we will remove the possibility of connecting as "sqlplus / as sysdba" i.e. we will block the OS level authentication by using a non privileged user "timepass" :D .
[timepass@adc2110341 dbhome_1]$ id
uid=60000(timepass) gid=60000(timepass) groups=60000(timepass)

This makes sure that OS authentication won't be used while connecting as sysdba.

First Case :-
Connecting when remote_login_passwordfile=EXCLUSIVE and password file present :-

1. Check if remote_login_passwordfile is either set to EXCLUSIVE/SHARED
SQL> show parameter pass

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

2. Now check if password file is present for your database at the default location $ORACLE_HOME/dbs by the name orapw$ORACLE_SID .
bash-3.00$ pwd
/scratch/aime1/app/aime1/product/11.2.0/dbhome_1/dbs
bash-3.00$ ls orapw*
orapworcl

In our case , it is present . We will look later on how to create/modify the same.

3. We can find the contents of the Password file by two methods :-

1. By Linux utility "strings"
bash-3.00$ strings -a orapworcl 
]\[Z
ORACLE Remote Password file
INTERNAL
765379FA336BE7F0
43CA255A7916ECFE
O!KfH
g[HLm

2. By the view v$pwfile_users
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

Hence , by default password file contains only user SYS in it with the password which we specify while creating it .

Now you can see INTERNAL as the user if displayed by Password file contents but not when you use the v$view . That happens because of the way v$PWFILE_USERS is defined . Lets look at it in more detail.

Question :- Why V$PWFILE_USERS doesn't contain INTERNAL ?
Solution :- We have a view V$FIXED_VIEW_DEFINITION and this view contains the definitions of all the fixed views (views beginning with V$/GV$)
SQL> desc v$fixed_view_definition
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
VIEW_NAME                                          VARCHAR2(30)
VIEW_DEFINITION                                    VARCHAR2(4000)

Now we will select the definition of "V$PWFILE_USERS" from this view :-
SQL> select VIEW_DEFINITION from v$fixed_view_definition where upper(VIEW_NAME) ='V$PWFILE_USERS';

VIEW_DEFINITION
--------------------------------------------------------------------------------
select  USERNAME , SYSDBA , SYSOPER, SYSASM from GV$PWFILE_USERS where inst_id =
USERENV('Instance')

AS we can see , this view is based on another fixed view named "GV$PWFILE_USERS" . Lets check its definition as well .
SQL> select VIEW_DEFINITION from v$fixed_view_definition where upper(VIEW_NAME) ='GV$PWFILE_USERS';

VIEW_DEFINITION
--------------------------------------------------------------------------------
select inst_id,username,decode(sysdba,1,'TRUE','FALSE'),  decode(sysoper,1,'TRUE
','FALSE'), decode(sysasm,1,'TRUE','FALSE')  from x$kzsrt where valid=1  and use
rname != 'INTERNAL'

Now if you notice the definition of this view , it is dervied from x$kzsrt which roughly stands for :-
x$kzsrt – read as “[K]ernel [Z]Security layer [R]emote Password File [T]able entries

And the select query is made in such a way that User INTERNAL is not selected. And hence , it doesn't appear in the V$PWFILE_USERS view.

The other important point to note is that User INTERNAL was de-supported in 9i ( when they de-supported SVRMGRL) , even though user still exists in Oracle Database 11g Release 2 .
So trying to connect as internal fails .
bash-3.00$ sqlplus internal as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jan 12 23:18:32 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Enter password: 
ERROR:
ORA-09275: CONNECT INTERNAL is not a valid DBA connection

4. Now trying to connect as sysdba using password file :-
[timepass@adc2110341 bin]$ sqlplus sys/welcome1 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 12 23:25:31 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Trying to connect from remote host :-

bash-3.00$ sqlplus sys/welcome1@adc_ORCL as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jan 12 23:26:22 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

This works fine .


Second Case :-
Connecting when remote_login_passwordfile=EXCLUSIVE and password file NOT present :-


1. Check if remote_login_passwordfile is either set to EXCLUSIVE/SHARED
SQL> show parameter pass

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

2. Now we will delete/rename the password file :-
bash-3.00$ mv orapworcl orapworcl.move

And if try to connect now as sysdba ,
[timepass@adc2110341 dbs]$ sqlplus sys/welcome1 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 12 23:33:03 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges


Third Case :-
Connecting when remote_login_passwordfile=NONE and password file present/NOT present :-


1. Modify the parameter remote_login_passwordfile and set it to NONE and bounce the database.
SQL> alter system set remote_login_passwordfile=NONE scope=spfile;

System altered.

Now check the value of the parameter :-
SQL> show parameter pass

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      NONE

2. Now try to connect as SYSDBA :-
[timepass@adc2110341 dbs]$ sqlplus sys/welcome1 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 12 23:37:28 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges

As we can see , second and third case are equivalent i.e. if remote_login_passwordfile is set to NONE or if it is set to EXCLUSIVE but the password file is not present , password file authentication is disabled and the only way to connect as sysdba is OS Authentication.

The other point to ntoe is we can't grant a user SYSDBA privilege if password file auth is disabled .
SQL> grant sysdba to dba1;
grant sysdba to dba1
*
ERROR at line 1:
ORA-01994: GRANT failed: password file missing or disabled

Modifying Password File :-


When you grant SYSDBA or SYSOPER privileges to a user, that user's name and privilege information are added to the password file. If the server does not have an EXCLUSIVE password file (that is, if the initialization parameter REMOTE_LOGIN_PASSWORDFILE is NONE or SHARED, or the password file is missing), Oracle Database issues an error if you attempt to grant these privileges.

A user's name remains in the password file only as long as that user has at least one of these two privileges. If you revoke both of these privileges, Oracle Database removes the user from the password file.

Lets try it :-

1. Create user dba1
SQL> create user dba1 identified by dba1;
User created.

SQL> grant connect to dba1;
Grant succeeded.

2. Check the pass file contents :-
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

3. Now grant sysdba to dba1 and check the contents again .
SQL> grant sysdba to dba1;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
DBA1                           TRUE  FALSE FALSE

4. Now try to connect as dba1 as sysdba
SQL> conn dba1/dba1 as sysdba
Connected.

Expanding the Password File :-


If you receive the file full error (ORA-1996) when you try to grant SYSDBA or SYSOPER system privileges to a user
SQL> grant sysdba to d;
grant sysdba to d
*
ERROR at line 1:
ORA-01996: GRANT failed: password file
'/scratch/aime1/app/aime1/product/11.2.0/dbhome_1/dbs/orapworcl' is full

You need to create a larger password file and regrant the privileges to the users.

Saturday, January 8, 2011

OS Level Authentication - UNIX

The version of my Database is :-


SQL> select version from v$instance;

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


Methods to connect as SYSDBA :-



If the DBA wants to start up an Oracle instance there must be a way for Oracle to authenticate this DBA. That is if (s)he is allowed to do so. Obviously, his password can not be stored in the database, because Oracle can not access the database before the instance is started up. Therefore, the authentication of the DBA must happen outside of the database.

To connect as "Database Administrator" i.e. sysdba , we can choose between operating system authentication or password files.
In this post , we will concentrate on "OS Auth" , next post with password file will be coming soon ..


OS Authentication (UNIX) :-



Connecting as SYSDBA :-

To connect as sysdba using OS Authentication ; your UNIX OS user must be a part of OSDBA group. Once the user is part of OSDBA group , irrespective of the username/password you provide , you successfully connect to sqlplus as SYS user as sysdba .

Example :-

The "timepass" user here is part of "DBA" group and hence , irrespective whether we provide the password or not ; or provide the wrong password, it connects successfully .


[timepass@adc2110341 bin]$ id
uid=60000(timepass) gid=8500(dba) groups=8500(dba)

[timepass@adc2110341 bin]$ ./sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 7 05:31:04 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "SYS"

[timepass@adc2110341 bin]$ ./sqlplus sys/asffg as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 7 05:31:04 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "SYS"


[timepass@adc2110341 bin]$ ./sqlplus sys/welcome as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 7 05:31:04 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "SYS"



Membership in the OSDBA or OSOPER group affects your connection to the database in the following ways:

1. If you are a member of the OSDBA group and you specify AS SYSDBA when you connect to the database, then you connect to the database with the SYSDBA system privilege.
2. If you are a member of the OSOPER group and you specify AS SYSOPER when you connect to the database, then you connect to the database with the SYSOPER system privilege.
3. If you are not a member of either of these operating system groups and you attempt to connect as SYSDBA or SYSOPER ( using OS Auth) , the CONNECT command fails.


Connecting as Normal User :-



Till now we saw connecting to the database as sysdba , and that is possible only if you belong to the above mentioned groups.
Second important part to OS Authentication is connecting to the database as normal user and not the DBA.

Example :-

If we consider timepass as a UNIX user which doesnt belong to the above mentioned privilege group and belong to normal user group ( timepass).

First , we will create the user timepass with default group :-


With root user :-




bash-3.00# /usr/sbin/useradd -d /scratch/timepass timepass

bash-3.00# passwd timepass
Changing password for user timepass.
New UNIX password:
BAD PASSWORD: it is too short
Retype new UNIX password:
passwd: all authentication tokens updated successfully.


Now login as timepass user and check the ID :-

bash-3.00$ su timepass
Password:

[timepass@adc2110341 bin]$ id
uid=60000(timepass) gid=60000(timepass) groups=60000(timepass)


Consider the cases :-

1. Connecting with normal UNIX user as normal DB user :-

[timepass@adc2110341 bin]$ ./sqlplus /

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 7 05:22:48 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


2. Connecting with normal UNIX user as SYSDBA user :-

[timepass@adc2110341 bin]$ ./sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 7 05:30:01 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

ERROR:
ORA-01031: insufficient privileges


Both the cases give an error and we are not able to connect .

Now if we want the UNIX user to connect to database as normal user , we need to define the user in the database as OS user.

Lets take an example of how we do it.

First from the SQL Prompt , we will check the prefix for the OS User :-

SQL> show parameter prefix

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$

Now we will define the user "timepass" using the prefix "ops$" :-

SQL> create user ops$timepass identified externally;
User created.

SQL> GRANT CONNECT TO ops$timepass;
Grant succeeded.


Now consider the 2 cases again :-

1. Connecting as normal user :-

[timepass@adc2110341 bin]$ ./sqlplus /

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 7 05:33:05 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "OPS$TIMEPASS"

2. Connecting as Sysdba :-

[timepass@adc2110341 bin]$ ./sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 7 05:33:32 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

ERROR:
ORA-01031: insufficient privileges

As we can see , connecting as sysdba fails because for OS Authentication to work , your user must be a part of privileged USER group ; which is negative in our case. But connecting as normal user succeeds because we have identified this user as DB user ( external user).


If we see one last thing , if your user belong to the DBA group and is identified externally , you can connect both ways :-

Change the group to dba :-

[timepass@adc2110341 bin]$/usr/sbin/usermod -g dba timepass

[timepass@adc2110341 bin]$ id
uid=60000(timepass) gid=8500(dba) groups=8500(dba)

[timepass@adc2110341 bin]$ ./sqlplus /

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 7 05:34:25 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "OPS$TIMEPASS"

[timepass@adc2110341 bin]$ ./sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 7 05:34:34 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "SYS"

One thing to notice is whenever you connect as sysdba using OS level authentication , show user will always give username as "sys".

Another important thing is you can't grant to sysdba privilege to externally identified user :-

SQL> grant sysdba to OPS$timepass;
grant sysdba to OPS$timepass
*
ERROR at line 1:
ORA-01997: GRANT failed: user 'OPS$TIMEPASS' is identified externally


REMOTE OS AUTHENTICATION and SQLNET.ORA File :-




REMOTE OS AUTHENTICATION



SQL> show parameter remote_os;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE

"remote_os_authent" :- Specifies whether remote clients will be authenticated with the value of the os_authent_prefix parameter.

SQLNET.ora



The "sqlnet.ora" file contains client side network configuration parameters. It can be found in the "$ORACLE_HOME/network/admin" or "$ORACLE_HOME/net80/admin" directory on the client. This file will also be present on the server if client style connections are used on the server itself.

Now we have few combinations for both of them :-

1. remote_os_authent=false

Remote users will be unable to connect without a password. IDENTIFIED EXTERNALLY will only be in effect from the local host. Also, if you are using OPS$ as your prefix, you will be able to log on locally with or without a password, regardless of whether you have identified your ID with a password or defined it to be IDENTIFIED EXTERNALLY.

1.1 SQLNET.ORA doesnt have "SQLNET.AUTHENTICATION_SERVICES=(NONE)"

"IDENTIFIED EXTERNALLY " user works fine from localhost . But when tried with same user from remote host , gives the error as :-

bash-3.00$ sqlplus /@adc_orcl

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Jan 8 02:36:51 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


1.2 SQLNET.ORA has "SQLNET.AUTHENTICATION_SERVICES=(NONE)"

There is no affect on remote host , because it is disabled anyway . But this entry disables local OS authentication as sysdba as well.

2. remote_os_authent=true

Allows other O/S authentication. Oracle assumes that the remote OS has authenticated the user. In order to use OPS$, remote_os_authent = TRUE and remote_os_roles = TRUE.

Lets change the parameter :-

SQL> alter system set remote_os_authent=TRUE scope=spfile;
alter system set remote_os_roles=TRUE scope=spfile;
System altered.

SQL>
System altered.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

In case of 11g , this parameter is deprecated , hence gives the warning.


2.1 SQLNET.ORA doesnt have "SQLNET.AUTHENTICATION_SERVICES=(NONE)"

2.2 SQLNET.ORA has "SQLNET.AUTHENTICATION_SERVICES=(NONE)"

Irrespective of SQLNET.ora entry , the output from remote host is :-

bash-3.00$ sqlplus /@adc_orcl

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Jan 8 02:45:27 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

bash-3.00$ sqlplus /@adc_orcl as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Jan 8 02:45:46 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

ERROR:
ORA-01031: insufficient privileges

The one conclusion from this irrespective of the entry in sqlnet.ora / remote_os_auth parameter ; remote host can't use OS Authentication to connect as sysdba and local host OPS$ user cant be disabled by modifying these two.

Wednesday, September 1, 2010

No Archive Mode

Backup and Recovery : Part 2

Backup Strategies
-------------------
Have you ever been in a state of confusion with the various backing up strategies like cold/hot or archive/no archive ?
At-least that was the case with me always . Very lately i found out that the main distinction is not between hot or cold but it is between archive mode and non archive mode .

You can check the mode in which your database is running :-
SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence           1


My database here is running in non-archive mode. And hence , we will deal with non-archive mode first.

Now coming to hot/cold backup ; hot backup is when you take the backup when your database is online and cold is when your database is offline i.e. properly shutdown . Hot backup is not possible in case of nonarchive mode .A proper shutdown of the database is required for nonarchive backup , even shutdown abort brings your system in an inconsistent state , hence shouldn't be used for cold backup.


NO-ARCHIVE MODE

What to Backup ?
-------------------

If you are not sure about the location of the datafiles and the control files , check the same by using this :-
SQL>  select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/scratch/aime1/app/aime1/oradata/orcl/system01.dbf
/scratch/aime1/app/aime1/oradata/orcl/sysaux01.dbf
/scratch/aime1/app/aime1/oradata/orcl/undotbs01.dbf
/scratch/aime1/app/aime1/oradata/orcl/users01.dbf
/scratch/aime1/app/aime1/oradata/orcl/example01.dbf

SQL> show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /scratch/aime1/app/aime1/orada
ta/orcl/control01.ctl, /scratc
h/aime1/app/aime1/flash_recove
ry_area/orcl/control02.ctl


Redo log files should not be backed up in no-archive mode since they are continuously over-written without archiving.

How to take Backup ?
---------------------

Shutdown the database properly forcing a checkpoint in the files.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


Go to the terminal and make a backup of all the files in another folder :-

bash-3.00$ pwd
/scratch/aime1/app/aime1/oradata/orcl
bash-3.00$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
bash-3.00$ ls *.dbf
example01.dbf system01.dbf undotbs01.dbf
sysaux01.dbf temp01.dbf users01.dbf
bash-3.00$ cp *.dbf ~/app/aime1/Backup/
bash-3.00$ cd control01.ctl ~/app/aime1/Backup/
bash: cd: control01.ctl: Not a directory
bash-3.00$ cp control01.ctl ~/app/aime1/Backup/
bash-3.00$ cd ~/app/aime1/Backup/
bash-3.00$ ls
control01.ctl sysaux01.dbf temp01.dbf users01.dbf
example01.dbf system01.dbf undotbs01.dbf

Why to recover?
------------------------------

Now go back to the sqlplus and start the database again

To check the default tablespace for a user , use
SQL> select USERNAME,DEFAULT_TABLESPACE from dba_users where username ='SCOTT';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
SCOTT                          USERS


SQL> create table test(a number);

Table created

SQL> insert into test values(1);

1 row created.

Hence the dbf file for the users tablespace i.e. USERS01.dbf is modified . Now perform a shutodwn again.

How to Recover?
----------------------

If we assume now that the dbf file for the users tablespace is lost/ corrupted because of some problem , the question is how do we recover it now with the available backup that we have.
There are different possibilities and going stepwise :-

STEP 1:- Copy the dbf file from the backup location to the original location.

First take a backup of the original dbf file (Best practice) and then copy the backup dbf file.

bash-3.00$ pwd
/scratch/aime1/app/aime1/Backup
bash-3.00$ cd ../oradata/orcl/
bash-3.00$ cp users01.dbf users01.dbf.bak
bash-3.00$ cp ~/app/aime1/Backup/users01.dbf users01.dbf

Now try to start the database.
SQL> startup;
ORACLE instance started.

Total System Global Area 2042241024 bytes
Fixed Size                  1337548 bytes
Variable Size            1056966452 bytes
Database Buffers          973078528 bytes
Redo Buffers               10858496 bytes
Database mounted.
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/scratch/aime1/app/aime1/oradata/orcl/users01.dbf'

It throws an error saying "Media Recovery is needed" . The database state at this point is "MOUNTED" but it could not be started.

Now if check the checkpoint number for the datafiles and the control files :-
SQL> select FILE#, CHECKPOINT_TIME, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER;

FILE# CHECKPOIN CHECKPOINT_CHANGE#
---------- --------- ------------------
1 01-SEP-10            1419740
2 01-SEP-10            1419740
3 01-SEP-10            1419740
4 01-SEP-10            1416309
5 01-SEP-10            1419740

SQL> select CHECKPOINT_CHANGE# from v$database;

CHECKPOINT_CHANGE#
------------------
1419740

The datafile #4 (Users01.dbf) is behind in SCN number than the other files and the control files.

If we try to recover database by running this ,
SQL> recover database
Media recovery complete.

It says that " Media Recovery is completed" .
Checking the alert_SID.log file :-
Contents:-
ALTER DATABASE RECOVER database
Media Recovery Start
started logmerger process
Parallel Media Recovery started with 2 slaves
Wed Sep 01 02:30:24 2010
Recovery of Online Redo Log: Thread 1 Group 1 Seq 40 Reading mem 0
Mem# 0: /scratch/aime1/app/aime1/oradata/orcl/redo01.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 41 Reading mem 0
Mem# 0: /scratch/aime1/app/aime1/oradata/orcl/redo02.log
Media Recovery Complete (orcl)
Completed: ALTER DATABASE RECOVER database

It shows that redo log files were used for the recovery of the database . That was possible because there wasnt much log switch happened after the backup , hence the SCN number was present in the log files for the recovery .
SQL> alter database open;

Database altered.

SQL>  select FILE#, CHECKPOINT_TIME, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER;

FILE# CHECKPOIN CHECKPOINT_CHANGE#
---------- --------- ------------------
1 01-SEP-10            1419743
2 01-SEP-10            1419743
3 01-SEP-10            1419743
4 01-SEP-10            1419743
5 01-SEP-10            1419743

SQL> select * from SCOTT.test;

A
----------
1

Suppose that there were many log switches after we took of cold backup of all the files.
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

Now in this case , if we try to run "Recover Database" then
QL> recover database;
ORA-00279: change 1416309 generated at 09/01/2010 01:48:54 needed for thread 1
ORA-00289: suggestion :
/scratch/aime1/app/aime1/flash_recovery_area/ORCL/archivelog/2010_09_01/o1_mf_1_
40_%u_.arc
ORA-00280: change 1416309 for thread 1 is in sequence #40


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log
'/scratch/aime1/app/aime1/flash_recovery_area/ORCL/archivelog/2010_09_01/o1_mf_1
_40_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

Now media recovery is not possible in this case .

It asks us about the location of the archived log files but our database is running in noarchivelog mode , hence we dnt have any archived logs. Hence, it gives an error that media recovery is not possible.
In this case, goto STEP 2.


STEP 2:- Using Backup Control File

Copy the backup control file to the original location .

bash-3.00$ cp control01.ctl control01.ctl.bak
bash-3.00$ cp ~/app/aime1/Backup/control01.ctl control01.ctl

Now try to start the database
SQL> startup;
ORACLE instance started.

Total System Global Area 2042241024 bytes
Fixed Size                  1337548 bytes
Variable Size            1056966452 bytes
Database Buffers          973078528 bytes
Redo Buffers               10858496 bytes
ORA-00214: control file
'/scratch/aime1/app/aime1/flash_recovery_area/orcl/control02.ctl' version 3557
inconsistent with file '/scratch/aime1/app/aime1/oradata/orcl/control01.ctl'
version 3503

It gives an error of the version mismatch between both the control files . Hence we need to copy the backup control file to other location as well.

bash-3.00$ cp ~/app/aime1/Backup/control01.ctl control01.ctl
bash-3.00$ cp /scratch/aime1/app/aime1/oradata/orcl/control01.ctl /scratch/aime1/app/aime1/flash_recovery_area/orcl/control02.ctl

Again starting the database :
SQL> startup;
ORACLE instance started.

Total System Global Area 2042241024 bytes
Fixed Size                  1337548 bytes
Variable Size            1056966452 bytes
Database Buffers          973078528 bytes
Redo Buffers               10858496 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/scratch/aime1/app/aime1/oradata/orcl/system01.dbf'
ORA-01207: file is more recent than control file - old control file

SQL> select FILE#, CHECKPOINT_TIME, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER;

FILE# CHECKPOIN CHECKPOINT_CHANGE#
---------- --------- ------------------
1 01-SEP-10            1423110
2 01-SEP-10            1423110
3 01-SEP-10            1423110
4 01-SEP-10            1416309
5 01-SEP-10            1423110

SQL> select CHECKPOINT_CHANGE# from v$database;

CHECKPOINT_CHANGE#
------------------
1416309

Checkpoint number in the control file is less than those in other dbf files .
Now if we try to recover saying "using backup controlfile" , it gives an error "RECOVER succeeded but OPEN RESETLOGS would get error below" :-

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
ORA-00279: change 1416309 generated at 09/01/2010 01:48:54 needed for thread 1
ORA-00289: suggestion :
/scratch/aime1/app/aime1/flash_recovery_area/ORCL/archivelog/2010_09_01/o1_mf_1_
40_%u_.arc
ORA-00280: change 1416309 for thread 1 is in sequence #40

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log
'/scratch/aime1/app/aime1/flash_recovery_area/ORCL/archivelog/2010_09_01/o1_mf_1
_40_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/scratch/aime1/app/aime1/oradata/orcl/system01.dbf'

Trying to open the database :-
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/scratch/aime1/app/aime1/oradata/orcl/system01.dbf'


Hence its not possible to do this without the redo log files .
In this case , go to STEP 3

STEP 3
: Copy all the files

Copy all the backup dbf files and ctl file to the original location

bash-3.00$ cp ~/app/aime1/Backup/*.dbf .

Now try to open the database with resetlogs :-
SQL> alter database open resetlogs;

Database altered.

ALert_SID. log file contents :-

alter database open resetlogs
RESETLOGS after incomplete recovery UNTIL CHANGE 1416309
Resetting resetlogs activation ID 1255868088 (0x4adb06b8)
Wed Sep 01 03:27:09 2010

Now check the checkpoint number :-

SQL> select FILE#, CHECKPOINT_TIME, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER;

FILE# CHECKPOIN CHECKPOINT_CHANGE#
---------- --------- ------------------
1 01-SEP-10            1416313
2 01-SEP-10            1416313
3 01-SEP-10            1416313
4 01-SEP-10            1416313
5 01-SEP-10            1416313

SQL> select CHECKPOINT_CHANGE# from v$database;

CHECKPOINT_CHANGE#
------------------
1416313


All the files have reached to the same checkpoint number .

If we try to access the tets table now , it gives an error that it doesnt exist .
SQL> select * from Scott.test
2  ;
select * from Scott.test
*
ERROR at line 1:
ORA-00942: table or view does not exist

This error comes because our database is restored to the time when last backup was taken and that time test table wasn't created.

CONCLUSION :-
------------------

From the above scenario , we can conclude that the media recovery is not possible in case of no-archive log, only instance recovery can be done. That also, our instance is recovered back to the time when last backup was taken , and all the changes made to the database after that are lost. Archiving redo logs i.e. archive mode is solution to this.

Friday, August 27, 2010

REDO Log and Checkpoint

I was reading about the data backup and recovery in Oracle DB and in the end , was left with all confusion. So finally decided to start from reading checkpoint/logfile concepts so that it helps in better understanding .

So you can consider this post as :-
Backup and Recovery : Concepts Part 1

REDO LOGFILE
-----------------
The Oracle server maintains the redo Oracle log files to minimize the loss of data in the Database in case of an uncontrolled shutdown.

Online redo Oracle log files are filled with redo records. A redo record, also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database.

For example, if you change a employee name in an employee table, you generate a redo record containing change vectors that describe changes to the data segment block for the table, the rollback segment data block, and the transaction table of the rollback segments.

Two views play an important part here namely , v$log and v$logfile.

v$logfile can be used to find the path of the redo log or their type (online/standby(used for standby database))
For more information on standby redo logs :-
http://www.pythian.com/news/581/oracle-standby-redo-logs/

 
SQL> select group#,type,member from v$logfile;

GROUP# TYPE MEMBER
--------------------------------------------------------------------------------
3 ONLINE /scratch/aime1/app/aime1/oradata/orcl/redo03.log
2 ONLINE /scratch/aime1/app/aime1/oradata/orcl/redo02.log
1 ONLINE /scratch/aime1/app/aime1/oradata/orcl/redo01.log


v$log can be used to find out if your redo logs are archived or not with archived column . And also to check the status of the the log files . There are different log status , few of the important ones are:-

1. UNUSED - Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.
2. CURRENT - Current redo log. This implies that the redo log is active. The redo log could be open or closed.
3. ACTIVE - Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
4. INACTIVE - Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.
5. INVALIDATED - Archived the current redo log without a log switch.

 
SQL> SELECT group#, sequence#, members, archived, status FROM v$log;

GROUP# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 7 1 NO CURRENT
2 5 1 NO INACTIVE
3 6 1 NO INACTIVE


Also to check which archivelog/no archivelog mode , we can use
 
SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

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

CHECKPOINT
----------------

A checkpoint performs the following three operations:

1. Every dirty block in the buffer cache is written to the data files. That is, it(DBWR) synchronizes the datablocks in the buffer cache with the datafiles on disk.
2. The latest SCN is written (updated) into the datafile header.
3. The latest SCN is also written to the controlfiles.

Every modification in the database (for example, an update) is given an unique system change number SCN, which is, therefore, an ever increasing integer. The latest SCN is immediately recorded in the controlfile. The modifications are logged in the online redo log by the log writer LGWR, but not necessarily in the datafiles.

v$datafile_header can be used to find out the checkpoint number in the datafiles .

 
SQL> select FILE#, CHECKPOINT_TIME, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER;

FILE# CHECKPOIN CHECKPOINT_CHANGE#
---------- --------- ------------------
1 27-AUG-10 829278
2 27-AUG-10 829278
3 27-AUG-10 829278
4 27-AUG-10 829278
5 27-AUG-10 829278


Latest checkpoint number can be found with the help of v$database

 
SQL> select CHECKPOINT_CHANGE# from v$database;

CHECKPOINT_CHANGE#
------------------
829278

If the control file is a backup file , checkpoint number in the file can be found with :-
 
SQL> select CONTROLFILE_CHANGE# from v$database;

CONTROLFILE_CHANGE#
-------------------
830797

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

REDO Log and Checkpoint
---------------------------

Now , "alter system switch logfile " forces a redo log switch to happen . A log switch also occurs when one online redo log has been filled and the next online redo log is going to be filled.

A log switch always triggers a checkpoint (that's what is written at maximum places) but if you see this carefully .

 
SQL> select FILE#, CHECKPOINT_TIME, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER;

FILE# CHECKPOIN CHECKPOINT_CHANGE#
---------- --------- ------------------
1 27-AUG-10 831582
2 27-AUG-10 831582
3 27-AUG-10 831582
4 27-AUG-10 831582
5 27-AUG-10 831582

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
831582

SQL> SQL> SELECT group#, sequence#, members, archived, status FROM v$log;

GROUP# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 13 1 NO INACTIVE
2 14 1 NO CURRENT
3 12 1 NO INACTIVE

SQL> alter system switch logfile;

System altered.

SQL> SELECT group#, sequence#, members, archived, status FROM v$log;

GROUP# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 13 1 NO INACTIVE
2 14 1 NO ACTIVE
3 15 1 NO CURRENT

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
831582

SQL> select FILE#, CHECKPOINT_TIME, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER;

FILE# CHECKPOIN CHECKPOINT_CHANGE#
---------- --------- ------------------
1 27-AUG-10 831582
2 27-AUG-10 831582
3 27-AUG-10 831582
4 27-AUG-10 831582
5 27-AUG-10 831582


There is no change in the checkpoint . Checkpointing in case of log switch happens if there are dirty buffers to be written to the datafile or in other words , SCN in the datafile header and in the control file don't match . Here since they were same , no checkpointing occurred even though log was switched.

Also , if we do a checkpointing , log switch doesn't happen but triggering a checkpoint brings the log files in "INACTIVE" state since they are no longer needed in instance recovery (may be needed in the media recovery) ( as it is written on the datafiles).

 
SQL> SELECT group#, sequence#, members, archived, status FROM v$log;

GROUP# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 13 1 NO CURRENT
2 11 1 NO INACTIVE
3 12 1 NO INACTIVE

SQL> alter system switch logfile;

System altered.

SQL> SELECT group#, sequence#, members, archived, status FROM v$log;

GROUP# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 13 1 NO ACTIVE
2 14 1 NO CURRENT
3 12 1 NO INACTIVE

SQL> alter system checkpoint;

System altered.

SQL> SELECT group#, sequence#, members, archived, status FROM v$log;

GROUP# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 13 1 NO INACTIVE
2 14 1 NO CURRENT
3 12 1 NO INACTIVE

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

Monday, April 5, 2010

Query Processing and Parsing

Queries in the Oracle need to be broken down into its sub-components and various checks needs to be performed on them before it can actually be executed.
The first phase of parsing a query includes Syntax check followed by Semantic check. Syntax check basically deals with the array of the SQL Grammar i.e. whether the statement/query written exists in the SQL Dictionary or is correct according to the grammar of the SQL Language while Semantic check checks for the existence of the object referred in the query i.e. Semantic check is, in short, the object check.

SQL> select * form tab;
select * form tab
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

Here what it returns is the expression saying, "FROM keyword not found " ... That means something is wrong with the syntax of the statement i.e. the structure of the query is not matching with what is expected. So, it failed at the syntax check ...

Now in semantic check, if the syntax is correct and the objects referred in the query are correct; then it generates the output otherwise not.
By the correctness the object, comes various considerations like privileges, existence etc. But the main point remains the same; the object should be visible to the user and the user should have necessary privileges on the object referred.

SQL> select * from tabl;
select * from tabl
*
ERROR at line 1:
ORA-00942: table or view does not exist

Semantic check failed since table doesn't exist even though the syntax of the query is correct.


After both the checks are done, Oracle checks if the exactly same query has been processed before i.e. if the work has already been done before. Shared pool, component of SGA (Shared/System Global Area) plays an important part here ... The SGA is a chunk of memory that is allocated by an Oracle Instance (during the no mount stage) and is shared among Oracle processes. The shared pool is used for objects that are shared among all users. Shared Pool has two important components: - Library Cache and Data Dictionary Cache. Data Dictionary cache stores part of the data dictionary because Oracle has to query the data dictionary very often while library cache is further divided into Shared SQL Area, PL/SQL Procedures and Control Structures (Latches and Locks) which stores the SQL text and the execution plan for the query.

Now if the query has been processed before and if it is found in library cache of SGA, then area called cursor area is already allocated in the library cache and execution plan exists there and thus we don’t need to perform the steps like Query Transformation, Plan generation again saving us lot of time ... This is what we called as "SOFT PARSE".

But if the query isn't there in library cache, then what we do, we call it as "HARD PARSE". In short, the steps that are there are: -
1. Oracle unpins the library cache latch and pin the shared pool latch.
2. Then it performs the query transformation steps (Query rewrite, bind variables etc)
3. After the query transformation, Oracle estimates the cost of the query in terms of I/O, Memory and CPU
4. And based on the cost, it generates multiple plans to execute the query and picks the most optimized plan and executes the query.

We can check for the execution plan selected for a query by using set autot command .It displays a report on the execution of successful SQL DML statements (such as SELECT, INSERT, UPDATE, DELETE or MERGE). The report can include execution statistics and the query execution path.

Syntax: -
SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

OFF does not display a trace report. ON displays a trace report. TRACEONLY displays a trace report, but does not print query data, if any. EXPLAIN shows the query execution path by performing an EXPLAIN PLAN. STATISTICS displays SQL statement statistics.

SQL> set autot on exp stat;
SQL> /

A
----------
1


Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01
|   1 |  TABLE ACCESS FULL| TEST |     1 |    13 |     2   (0)| 00:00:01
------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
3  consistent gets
0  physical reads
0  redo size
415  bytes sent via SQL*Net to client
416  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

Execution Plan gives us the cost and the rows etc while statistics gives us the more physical reads and blocks information.


There is another type of parsing also, called as "SOFTER SOFT PARSE". It makes use of the initialization parameter session_cached_cursors. If this parameter is set to some value, then in that case; Oracle takes you query, looks into the session cursor cache, picks up the cursor and reuses it. This involves lesser work, even lesser than SOFT Parse.

SQL> show parameter session_cached;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------------
session_cached_cursors               integer     50

If it is enabled, then two parameters play an important part from v$statname :- 

SQL> select a.name, b.value
2  from v$statname a, v$mystat b
3  where a.statistic# = b.statistic#
4  and lower(a.name) like '%cursor ca%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 0
session cursor cache count                                              0

So when a query is parsed for three times and caching of cursors is enabled, it increases the cursor cache count and caches the cursor ... And from next time onwards, uses that cursor increasing the cache hits count.


SQL> select a.name, b.value
2  from v$statname a, v$mystat b
3  where a.statistic# = b.statistic#
4  and lower(a.name) like '%cursor ca%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 0
session cursor cache count                                              1

SQL> select * from test;

A
----------
1

SQL> select a.name, b.value
2  from v$statname a, v$mystat b
3  where a.statistic# = b.statistic#
4  and lower(a.name) like '%cursor ca%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 1
session cursor cache count                                              3

( Cache Count increased) 


SQL> select * from test;

A
----------
1

SQL> select a.name, b.value
2  from v$statname a, v$mystat b
3  where a.statistic# = b.statistic#
4  and lower(a.name) like '%cursor ca%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 3
session cursor cache count                                              3


(Cache Hit increasing ... It increased twice, once for test query and another to display the statistics)...



So, in short, it has three types of parsing: -

1. HARD Parse: - First time query is being executed, parse it (syntax check, semantic check), hash it, look in the shared pool, not present, perform transformations, optimize it etc....
2. SOFT Parse: - Executed before, parse it, hash it, look in the shared pool, present, use it.
3. SOFTER soft Parse: - Look into session cursor cache, found, uses it. (But only if session_cached_cursors set to some value).

In database system, the ratio of SOFT Parse/HARD Parse should be very high since it implies the same query was reused again and again and less work was done.

Friday, April 2, 2010

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