Wednesday, October 10, 2012

Startup And Shutdown Modes Of An Oracle Database

An oracle database can be started in various modes. Each mode is used by the DBA's to perform some specific operation in the database.

Startup no mount: We can bring database to nomount state from shutdown state. When we start an oracle database in nomount state then oracle will create the oracle instance. The instance is created by reading all the values from a file known as init.ora parameter file. Based on the values from this file oracle will allocate the sga in the RAM and start the background processes.

Command to start database in nomount state:

Sql>startup nomount

The dba uses the no mount state to

1) Create an oracle database
2) Re-create a controlfile for an existing database

Mount State: From no mount the dba can take the database to mount state. In the mount state oracle will open the controlfile. After opening the control file oracle will read the path of the datafiles and log files from the control file.
To mount a database directly from shutdown state we issue

Sql> startup mount

To mount a database from a started state (nomount state)

Sql> alter database mount;

1) the mount state is used to recover a database that has crashed due to media failure.
2) the mount state is also used by the dba to enable archiver process.
3) it is also used by the dba to create a standby controlfile for configuring a standby database using dataguard.

Open state:

After mounting the database the dba can take the database to open state, when we open a database then a check will performed by oracle i.e, called sanity check. Sanity check is nothing but reading the path and filenames of the dbf's and log's from the controlfile and checking wether those files are physically available in the same locations at the o/s level. Incase any of the physical files is missing then sanity check will fail. If the sanity check is successful then oracle will begin to open each dbf one by one, when oracle opens the dbf it will check the scn (system change number) that is available in the dbf header and cross verify that scn with the scn in the controlfile.

IF the scn is the same then that file can be opened successfully, this way oracle will compare all the scns for all the files with the scn in the controlfile, only when all scns are matched with controlfile scn then only can the database be opened.

Incase the scns are not matched that means that the database is in an inconsistent state due to improper shutdown previously.
In this case oracle will try to recover the database with automatic crash recovery, if that also fails then oracle will ask the dba to manually recover the datbase using media recovery methods.

We can open an already mounted database by below command.

Sql>alter database open;

(or)

We can directly go from a shut database to an open database by typing below command.

Sql>startup;

When we open the database oracle will,

1) open each datafile one at a time.
2) on opening a datafile oracle will check the scn in the file header with the scn in the control file.

If all the scn's in the dbf's are matched with the scn in the controlfile then the db can be opened for use.
But if any of the file scn is not matching with control file scn then oracle will ask to recover that file and database cannot be opened.

Startup restrict:

If we start an oracle database in restricted mode then only those users who have restricted session privilege will be able to connect to the database.

Sql>startup restrict;
Sql>alter system disable restricted session;
Sql> alter system enable restricted session;

Suspending a database:

If we want to suspend all i/o operations.

Sql> alter system suspend;
Sql> select database_status from v$instance;

Database_status
-----------------
Suspended

Sql> alter system resume;

Sql> select database_status from v$instance;

Database_status
-----------------
Active

Quiesce database:

Quiesce a database gives us the opportunity to put our database in a single user mode without shutting down the database.

Startup mount restrict:

This mode has been introduced from oracle 10g onwards. If we want to drop the database then we mount the db it in this mode.

*the nomount state is used by the dba to create a new oracle database.
*the mount state is used by the dba to perform recovery
*the open state is used by the dba and programmers to work with the database in a normal way.

Shutdown modes in oracle:

Shutdown normal or shutdown or shut

When this command is issued then oracle will not allow any new connections/sessions to the database and will wait untill all the exising sessions log off.

There after it will save all the commited transactions and then shut the database.

A common scn number will be updated to controlfiles and datafiles before the database shutsdown.

Shutdown immediate:

When this command is issued then oracle will not allow any new connections/sessions to the database and will forcefully disconnect the existing connected users from the database and rollback all uncommited transactions and shut the database.

A common scn number will be updated to controlfiles and datafiles before the database shutsdown.

Shutdown transactional:

When this command is issued then oracle will not allow any new connections/sessions to the database and will wait for the users to commit/rollback their open transactions after which oracle will shut the database.

A common scn number will be updated to controlfiles and datafiles before the database shuts down.

Shutdown abort:

When this command is issued then oracle will not allow any new connections/sessions to the database and will kill the oracle instance in a abrupt manner.

When this command is issued all the uncommited transactions will be lost and oracle will not be able to update a common scn to all the files of the database. When we restart the db after a shut abort then oracle smon will have to perform crash recovery.
Shut abort is used when we have a loss of files at the o/s level.

We cannot use a normal shutdown as oracle will always write unsaved data to the file before shutting the database, but in this case our files are lost so oracle cannot find the files to write the data in the sga, before shutdown, therefore it will not allow the database to be shut so we have to shutdown abort.

Startup force:

This command will perform a shut abort and then executes a startup. This command must be avoided as it calls for crash recovery.

Important note:

When ever we are shutting a database in a normal way then before shutting the oracle database, oracle will write a common scn to the file headers of the datafiles and to the controlfile.

But incase of a shut abort oracle does not get the chance to write the common scn thus when we restart the database then oracle will find that the scn does not match for the data files and the control file. Thus oracle will call smon to perform 'crash recovery' or 'instance recovery'.


Startup and Shtdown demo:

SQL> CONN /AS SYSDBA
Connected.

SQL> SELECT COUNT(*) FROM TAB;

COUNT(*)
----------
3492

1 row selected.

SQL> SHOW USER
USER is "SYS"

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> EXIT
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

[chow@oraserver ~]$
[chow@oraserver ~]$ echo $ORACLE_SID
chow

[chow@oraserver ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 22 08:39:06 2008

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

Connected to an idle instance.

SQL> exit
Disconnected

[chow@oraserver ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 22 08:39:14 2008

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

Connected to an idle instance.

SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area 79691776 bytes
Fixed Size 1217812 bytes
Variable Size 62917356 bytes
Database Buffers 12582912 bytes
Redo Buffers 2973696 bytes

SQL> SELECT STATUS FROM V$INSTANCE;

STATUS
------------
STARTED

SQL> ALTER DATABASE MOUNT;

Database altered.

SQL> SELECT STATUS FROM V$INSTANCE;

STATUS
------------
MOUNTED

SQL> SHUTDOWN
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area 79691776 bytes
Fixed Size 1217812 bytes
Variable Size 62917356 bytes
Database Buffers 12582912 bytes
Redo Buffers 2973696 bytes
Database mounted.

SQL> SELECT STATUS FROM V$INSTANCE;

STATUS
------------
MOUNTED

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> SELECT STATUS FROM V$INSTANCE;

STATUS
------------
OPEN

SQL> ALTER DATABASE CLOSE;

Database altered.

SQL> SELECT STATUS FROM V$INSTANCE;

STATUS
------------
MOUNTED

SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-16196: database has been previously opened and closed


SQL> ALTER DATABASE DISMOUNT;

Database altered.

SQL> SELECT STATUS FROM V$INSTANCE;

STATUS
------------
STARTED

SQL> ALTER DATABASE MOUNT;
ALTER DATABASE MOUNT
*
ERROR at line 1:
ORA-00750: database has been previously mounted and dismounted


SQL> SHUTDOWN
ORA-01507: database not mounted


ORACLE instance shut down.

SQL> STARTUP
ORACLE instance started.

Total System Global Area 79691776 bytes
Fixed Size 1217812 bytes
Variable Size 62917356 bytes
Database Buffers 12582912 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP RESTRICT
ORACLE instance started.

Total System Global Area 79691776 bytes
Fixed Size 1217812 bytes
Variable Size 62917356 bytes
Database Buffers 12582912 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.

SQL> CREATE USER SCOTT IDENTIFIED BY TIGER;

User created.

SQL> GRANT CONNECT,RESOURCE TO SCOTT;

Grant succeeded.

SQL> CONN SCOTT/TIGER
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege


Warning: You are no longer connected to ORACLE.
SQL> CONN /AS SYSDBA
Connected.
SQL> GRANT RESTRICTED SESSION TO SCOTT;

Grant succeeded.

SQL> CONN SCOTT/TIGER
Connected.

SQL> SHOW USER
USER is "SCOTT"

SQL> CONN /AS SYSDBA
Connected.

SQL> REVOKE RESTRICTED SESSION FROM SCOTT;

Revoke succeeded.

SQL> CONN SCOTT/TIGER
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege


Warning: You are no longer connected to ORACLE.

SQL> CONN /AS SYSDBA
Connected.

SQL> ALTER DATABASE RESTRICTED SESSION DISABLE;
ALTER DATABASE RESTRICTED SESSION DISABLE
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE


SQL> ALTER DATABASE DISBALE RESTRICTED SESSION;
ALTER DATABASE DISBALE RESTRICTED SESSION
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE


SQL> ED
Wrote file afiedt.buf

1* ALTER DATABASE DISABLE RESTRICTED SESSION

SQL> /
ALTER DATABASE DISABLE RESTRICTED SESSION
*
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL> ED
Wrote file afiedt.buf

1* ALTER SYSTEM DISABLE RESTRICTED SESSION
SQL> /

System altered.

SQL> CONN SCOTT/TIGER
Connected.

SQL> CONN /AS SYSDBA
Connected.

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

System altered.

SQL> CONN SCOTT/TIGER
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege


Warning: You are no longer connected to ORACLE.

SQL> CONN /AS SYSDBA
Connected.

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

System altered.

SQL> ALTER DATABASE SUSPEND;
ALTER DATABASE SUSPEND
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE


SQL> ALTER SYSTEM SUSPEND;

System altered.

SQL> ALTER SYSTEM RESUME;

System altered.

SQL>


Logfile Architecture and Logwriter Process

After completion of any DML,DDL or TCL operation like insert, drop or commit, oracle writes the blocks (user blocks and undo blocks) and the original sql statement to the redo log buffer. This process repeats for every sql statement execution.

The redo log buffer is a small memory area thus it would fill up with data very rapidly, when ever the redo log buffer becomes 1/3rd filled up with data then oracle calls the log writer process.

The log writer process will move or write out the content of the log buffer to a redo log group's, log files.
Log groups are selected for writing by the lgwr based on the log group status.

Log groups have 3 status values

1) inactive
2) current
3) active

Inactive status:

It indicates that the log group is empty and can be used by the lgwr for writing data.
When ever an oracle database is started up then all its log groups would be in the inactive status. The lgwr usually picks up the first log group to begin writing transactions.

Current status:

It indicates that the log group is currently being written to it.
Once a log group is filled up with data then oracle can no longer write data to the log files of that group, at this point in time the lgwr stops writing to that group and begins writing to the next group who's status is inactive. This is known as a log switch.

Once the log group is filled up the status is changed to "active".

When ever a log switch occurs then the database writer process is called and its job is to write out the content of the filled up log group to the respective datafiles, it does this by reading the same data from the data buffer cache and writing that data to the datafiles.
After dbwr has finished writing the data to the datafiles from the data buffer then it signals the logwrite process to clear the active log group of its contents.
The data that is written to the datafiles by the database writer may be commited or uncommited data.

One oracle database will by default have 1 dbwr process but if needed the dba can enable upto 20 dbwr's.
Increasing the number of dbwr's will increase the speed of writing data buffer cache data to the datafiles.

The reason why oracle delays a write to the datafile and instead writes all data to the log groups is that, log files are small in size and thus for every small transaction oracle need not open the large datafiles and instead write to the smaller log files to gain performance.

The lgwr process is also called in the following scenarios:

1) When the redo log buffer is 1/3 rd full.
2) The lgwr has a 3 sec timeout value, where in every 3 seconds it is called to check the redo log buffer for any transactions.
3) The lgwr is also called when ever a user issues a commit statement.
A commit when written to the redo log buffer will cause the lgwr to wake up and write out all the content of the redo log buffer to the current log group.
Once the commit has been written to the log file then the user will get an acknowledgement of "commit complete"


The dbwr process is called in the following scenarios:

1) Every 3 seconds (dbwr time out value).
2) The dbcache has a threshold value, i.e. If 80% of the dbcache is filled up, then the dbwr process is called to empty the dbcache.
3) The dbwr is called when ever a user queries a very huge table, to make space for that table to be accomodated into the dbcache.
4) The dbwr is called before making a tablespace offline to write out the content of that tablespace to its datafiles.
5) The dbwr is called to write the dirty buffers to their respective datafiles before the database is going to shutdown.

Archiver process:

This is an optional process, if enabled by the dba then its job is to write or copy a filled up online redo log file to a location known as the archive log destination, before the log switch occurs.

The logfile will be copied with a unique name and the archiver process will continue to copy the logfiles as long as the database is running.

Archiver process will copy the logfile with a .arc file extention. These files can be used by the dba to perform media recovery incase of a media failure and loss of data due to deletion of the physical files.


Oracle File Structure

The file structure of an oracle database will consist of the following files.

1) Control file
2) Redo log file
3) Datafile

Control file:

The control file will have .ctl file extention. There must be a min of 1 control file for a database. We can have upto 8 control files (multiplexing) in different locations for safety. Oracle recommends a minimum of atleast two controlfiles for a database, each controlfile on a separate hard disk drive, multiplexing is keeping mirror copies of the same file in different locations. Incase one of the controlfiles gets deleted then the database will stop functioning at that time.

The solution to this problem is,

1) Remove the entry of the damaged controlfile from the database itself and restart the database and run it with the help of the remaining controlfiles.
2) Shut the database, replace the damaged disk, copy the existing controlfile from the other location , rename the file and restart the database.

The controlfile is the first file that is opened by a database, after opening this file oracle can find the other files like redo logs and datafiles.
The control file is a very important file it will keep critical info regarding the database like,

1) Name and database id of the database.
2) Path and file name of all the datafiles and redo logfiles.
3) Status of the last shutdown of the db (wether db shutdown gracefully or ungracefully).
4) Date and time of creation of the database.
5) System change number (scn) - unique number generated by oracle database every 3 seconds. it is used by oracle to determine recoveribility of a database after a database crash has occured.
6) Incase a dba calls recovery manager (rman) tool to perform a backup of a database, then after completion of that backup, rman will write a record to the database controlfile. That record contains information about the backup taken by rman, this information contains the address of the backup, label given to the backup, size of the backup, scn information generated during the backup and archive information.

Tuesday, October 9, 2012

Tablespace Management in Oracle


1) Tablespace belongs to the logical database. The tablespace will be identified by a name and is accessible only when the database is up and running.

2) A tablespace is a collection of one or more datafiles.

3) One oracle database may have several tablespaces.

4) The tablespaces will contain the data of the tables , the table data is kept within the datafiles of the tablespace.

5) Whenever we insert data into a table that data is transferred to an underlying tablespace which will inturn write that data to the underlying datafiles that are attached to that tablespace.

6) One datafile can belong only to one tablespace at a time.

7) More than one table can store their data in the same tablespace. One table can exist only in one tablespace at a time unless it is a partitioned table.

8) If the datafiles of a tablespace become full we can add new datafiles to that tablespace at any point in time.one tables data can span across many datafiles of the same tablespace.

9) By default when we create a new oracle database then some default tablespaces will be created. They are
  a) System tablespace
  b) Sysaux tablespace
  c) Undo tablespace
  d) Temporary tablespace
  e) Default permanent tablespace/users tablespace

10) The size of a tablespace is the sum total of the size of all the datafiles under that tablespace.

11) The smallest unit of data storage in an oracle database is a datablock.

12) The oracle datablock is a logical division of a datafile.

13) The size of an oracle datablock will be in multiples of the o/s block size.

14) The oracle datablock size can vary upto 2 to the power 'n' kb where 'n' ranges from 1 to 5.

15) The default size of a datablock in oracle 10g is 8kb and incase of 9i it is 2kb.

16) One datafile will have all blocks of the same size.

17) All datafiles belonging to one tablespace must have data blocks of the same size.

18) One oracle database can have tablespaces of different block sizes.

Tablespaces can be manipulated with the following sql commands,

1. CREATE
2. DROP
3. ALTER

Examples for Create Tablespace:

Permanent tablespace:

SQL> create tablespace sample datafile '/u01/oradata/sample01.dbf' SIZE 100M autoextend on;

Create more than one datafile with a single create tablespace command:

SQL> create tablespace sample datafile '/u01/oradata/sample01.dbf' size 100M autoextend on,
'/u01/oradata/sample02.dbf' SIZE 100M AUTOEXTEND ON,
'/u01/oradata/sample03.dbf' SIZE 100M AUTOEXTEND ON logging extent management local;

Temporary Tablespace:

SQL> Create temporary tablespace temp01 tempfile '/u01/oradata/TEMP01.DBF' size 2048m;

Undo tablespace:

SQL> create undo tablespace UNDOTBS datafile '/u01/oradata/undotbs01.dbf' size 2048M;

Big file tablespace (Introducing in Oracle10g):

SQL> Create bigfile tablespace bigtbs datafile '/u01/oradata/bigtbs01.dbf' SIZE 100G;

Extend Size of a tablespace:

SQL> alter database datafile '/u01/oradata/sample01.dbf' resize 1024M;

Adding datafile:

SQL> alter tablespace add datafile '/u01/oradata/sample02.dbf' size 1024M;

Tablespaces have two status

1. ONLINE (DEFAULT STATUS OF A TABLESPACE)
2. OFFLINE

Examples for making tablespace to offline and online:

SQL> alter tablespace sample offline;
SQL> alter tablespace sample online;

An online tablespace has 2 modes

1. READ WRITE (Default mode of a ts)
2. READ ONLY

Examples for making tablespace to Read Write and Read only mode:

SQL> alter tablespace sample read only;
SQL> alter tablespace sample read write;

Tablespace can have two logging modes

1. LOGGING (Tablespace is logging its transactions to a logfile). (default)
2. NOLOGGING (Tablespace is not logging its transactions to a logfile).

Drop tablespaces:

SQL>drop tablespace sample including contents and datafiles;

Tablespace related data dictionary views:

1. DBA_TABLESPACES
2. DBA_DATA_FILES
3. V$DATAFILES
4. V$TEMPFILE
5. DBA_SEGMENTS
6. DBA_EXTENTS
7. USER_SEGMENTS
8. USER_EXTENTS
9. SM$TS_FREE
10. SM$TS_USED

Monday, October 8, 2012

Setup Passwordless SSH between two servers in LINUX


SSH or Secure Shell is a network protocol that allows data to be exchanged using a secure channel between two networked devices.

The encryption used by SSH provides confidentiality and integrity of data over an insecure network, such as the Internet.

SSH was designed as a replacement for Telnet/rlogin/rsh and other insecure remote shells, which send information, notably passwords, in plaintext, leaving them open for interception.

Setup SSH Configuration:

Step #1: Hostnames are HYD01 and HYD02

Step #2: Generating Identity key pair on HYD01,

a) Create a directory .SSH under /home/oracle

$ mkdir .ssh

b)Give permissions to .ssh

$ chmod 755 .ssh

c) Generate RSA key pair

$ /usr/bin/ssh-keygen -t rsa

d) Generate DSA key pair

$ /usr/bin/ssh-keygen -t dsa

$ cd .ssh

$ ls

id_dsa id_dsa.pub id_rsa id_rsa.pub

Step #3: Add rsa and dsa keys to HYD01

cat id_dsa.pub > HYD01
cat id_rsa.pub >> HYD01

Now same steps follow on Host HYD02.

Step #4: Generating Identity key pair on HYD02,

a) Create a directory .SSH under /home/oracle

$ mkdir .ssh

b)Give permissions to .ssh

$ chmod 755 .ssh

c) Generate RSA key pair

$ /usr/bin/ssh-keygen -t rsa

d) Generate DSA key pair

$ /usr/bin/ssh-keygen -t dsa

$ cd .ssh

$ ls

id_dsa id_dsa.pub id_rsa id_rsa.pub

Step #5: Add rsa and dsa keys to HYD02

cat id_dsa.pub > HYD02
cat id_rsa.pub >> HYD02

Step #6: Copy public keys from host HYD01 to HYD02

scp HYD01 oracle@HYD02:/home/oracle/.ssh

Step #7: Add HYD01 and HYD02 public keys to authorised_keys

cat HYD01 > authorized_keys
cat HYD02 >> authorized_keys

Give permissions to authorized_keys

$chmod 644 authorized_keys

step #8: Copy authorized_keys from HYD02 to HYD01

scp authorized_keys oracle@HYD01:/home/oracle/.ssh

In Hyd01 also give permissions for authorized_keys

$chmod 644 authorized_keys

Now you can copy/move files from HYD02 to HYD01 and vice versa without password.


Some Most Popular Articles