Oracle Performance - 2

Oracle user creation hanging.

One day, I was doing my work and my application team called me regarding slow response of database. I kept my other less prioritized work in one side and started to analyse the things. I got a login to the database server and then called application team again to ask, what exactly they are facing. They told me, that "My account creation script is hanging. Which is working the since last 10 minutes. Account creation is not happening."

Normally, what DBA do? I started exactly the same thing. Checked locking session issue. Found no locking issue. Then started to check alert logfile. But no luck. No errors printed in alert logfile yet. Then I checked server Load average with uptime command, and found it to <1. So, I was wondering, that which is the cause. I goggled for 10 minutes but no luck. Account creation is hanging and no errors found in database server.

Then I started to think from ground level. When any query can not give the result? I answered my self, that when it can not have resource, and then I checked top command in my database server and surprisingly checked that my database server CPU utilization 100% because of one inactive session. I killed it immediately and CPU utilization decreased. Application team started to work fluently.

Some times our core knowledge can only help to come up with perfect solutions.

Oracle Performance - 1

Active Session History (ASH) Performed An Emergency Flush Messages In The Alert Log

One day suddenly, I got a warning on my RAC system that,

"Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is <some value> bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query: select total_size,awr_flush_emergency_count from v$ash_info;"

It's also not a warning, I was just checking alert log and found this lines. I did some googling and found the some activity on system causes more active sessions, therefore filling the ASH buffers faster than usual causing this message to be displayed. It is not a problem per session, just indicates the buffers might need to be increased to support peak activity on the database.

use below command to overcome this lines.

alter system set "_ash_size"=(current_value+50% of current_value);

Now, the question is how to get current _ash_size?

Here is the query.

select total_size from v$ash_info;

Note : The value from above query is in BYTES. So, do accordingly.

Oracle Architecture - 3

Oracle Instance

An Oracle instance consists of the System Global Area (SGA) memory structure and the background processes used to manage a database. An instance is identified by using methods specific to each operating system. The instance can open and use only one database at a time.

Oracle Architecture - 2

Oracle Server

The Oracle server can run on a number of different computers in one of the following ways:
• Client-Application Server-Server
• Client-Server
• Host-Based

Client-Application Server-Server:

(Three-tier) Users access the database from their personal computers (clients) through an application server, which is used for the application’s processing requirements.

Client-Server: 

(Two-tier) Users access the database from their personal computer (client) over a network, and the database sits on a separate computer (server).

Host-Based: 

Users are connected directly to the same computer on which the database resides.

Oracle Architecture - 1

Oracle Architectural Components

The Oracle architecture includes a number of primary components, which are discussed further.

• Oracle server: 

There are several files, processes, and memory structures in an Oracle
server; however, not all of them are used when processing a SQL statement. Some are used to improve the performance of the database, ensure that the database can be recovered in the event of a software or hardware error, or perform other tasks necessary to maintain the database. The Oracle server consists of an Oracle instance and an Oracle database.

• Oracle instance: 

An Oracle instance is the combination of the background processes and memory structures. The instance must be started to access the data in the database. Every time an instance is started, a System Global Area (SGA) is allocated and Oracle background processes are started. Background processes perform functions on behalf of the invoking process. They consolidate functions that would otherwise be handled by multiple Oracle programs running for each user. The background processes perform input/output (I/O) and monitor other Oracle processes to provide increased parallelism for better performance and reliability.

• Oracle database: 

An Oracle database consists of operating system files, also known as database files, that provide the actual physical storage for database information. The database files are used to ensure that the data is kept consistent and can be recovered in the event of a failure of the instance.

• Other key files: 

Nondatabase files are used to configure the instance, authenticate privileged users, and recover the database in the event of a disk failure.

• User and server processes: 

The user and server processes are the primary processes involved when a SQL statement is executed; however, other processes may help the server complete the processing of the SQL statement.

• Other processes: 

Many other processes exist that are used by other options within Oracle, such as Advanced Queuing, Real Application Clusters, Shared Server, Advanced Replication, and so on. These processes are discussed within their respective courses.

Oracle Storage - 1

How to resize a physical disk or LUN and an ASM DISKGROUP

Beware : Please read whole carefully

When the space in the LUN is exhausted, in the storage side it is a simple task adding new disks to the current LUN.  Now, it is required  the operating system recognize the new space.

In Linux in order to work on a physical disk, it requires at least one partition.  The fdisk command is used to display and manipulate the partitions.

fdisk -l /dev/sdg returns:

[root@arlnx2 root]# fdisk -l /dev/sdg

Disk /dev/sdg: 9105 MB, 9105018880 bytes

64 heads, 32 sectors/track, 8683 cylinders

Units = cylinders of 2048 * 512 = 1048576 bytes



  Device Boot    Start       End    Blocks   Id  System

/dev/sdg1             1      1908   1953776   83  Linux


For this particular example we are using a disk of 9g but only 2gb have been used.

Also a diskgroup has been created using this disk:

SQL> select name,path,total_mb,free_mb from v$asm_disk;NAME PATH TOTAL_MB  FREE_MB 

NAME PATH TOTAL_MB FREE_MB
DG1_0000 /dev/raw/raw4 1500 90 

The previous output shows that most of the space has been allocated, so assuming that there are not ASM disks 
available, then the LUN will have to be expanded at the OS level and finally at the ASM level resize the diskgroup.

 The first step will be adding space to the LUN. In this exercise we are using a DAS (Direct Attached Storage),
using SCSI interface, but on SAN or NAS the steps will be similar.

As we saw in the output of fdisk command, the disk has a capacity of 9gb but only 2gb have been used
for the partition.

In order to use more space, the partition has to be recreated.  This operation is at the partition table level,

which is stored in the first sectors of the disk.  Changing the partition table is not going to affect the data.

In other words, is extending the partition.

The general steps are:

1. resize LUN from storage
2. pick up new size from OS
3. in rolling fashion, shutdown ASM instances.
4. Only when all ASM have been recycled, you should resize the said disk on 1 ASM instance.

*Now, some details/examples to resize the LUN in Linux:

A. Delete the current  partition.
B. Recreate the partition with the new size.

The device /dev/raw/raw4 is attached to the physical disk /dev/sdg.  fdisk command is used to modify the partition table.  Use d to delete the partition, n to create the partition and specify the new size.  Finish the operation using w to write the partition table..

At this point the partition has been configured but it requires the operating system recognize the new size.

When the LUN is ready at the OS level and the ASM instance has been recycled, the next step is resizing the diskgroup at the ASM level:

SQL> alter diskgroup DG1 resize disk dg1_0000 size 8000m;
 Note: this will not trigger a rebalance.

*Now these steps are dangerous to perform

This is what I got a generalized answer by searching a lot. But the main thing is once you are delete the partition you will loose all the data on it. So please do it only if it's empty, or its not a part of any striping process of oracle diskgroup.



Enjoy !!

Oracle Backup - 2

OERR: ORA 1244 unnamed datafile(s) added to controlfile by media recovery

Error: ORA 1244
Text: "unnamed datafile(s) added to controlfile by media recovery"
-------------------------------------------------------------------------------
Cause:  Media recovery with a backup controlfile or a controlfile that
          was rebuilt, encountered the creation of a datafile that was not
          in the controlfile. An entry has been added to the controlfile
          for the new datafiles, but with the file name UNNAMEDnnnn, where
          nnnn is the file number. Attached errors describe the file names
          that were originally used to create the files. 
Action: Rename the files to valid file names and resume recovery. If
          necessary the command ALTER DATABASE CREATE DATAFILE may be used
          to create a file suitable for recovery and do the rename. If the
          file is not going to be recovered then take it offline with the
          DROP option.

Scenario when the controlfile is lost and there was a datafile added to an existing table space after backup of the controlfile was taken so the controlfile does not know about the new datafile.

1) Start database in nomount state
SQL> connect /as sysdba

SQL> startup nomount
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 2019672 bytes
Variable Size 109055656 bytes
Database Buffers 96468992 bytes
Redo Buffers 2170880 bytes

2) Connect to rman to restore control file, to restore the controfile it requires setting DBID
$ $ORACLE_HOME/bin/rman

RMAN> SET DBID 1998785623

executing command: SET DBID

run {
restore controlfile from '/u01/datafile/rmanbkp/TEST_3llv5k9u_117';
}

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 13-JAN-14
using channel ORA_DISK_1

channel ORA_DISK_1: looking for autobackup on day: 20101212
channel ORA_DISK_1: autobackup found: /u01/datafile/rmanbkp/c-1998785623-20101212-01
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u01/datafile/control1.ora
output filename=/u01/datafile/control2.ora
output filename=/u01/datafile/control3.ora
Finished restore at 13-JAN-14

3) Mount database so one can recover the database
SQL> alter database mount;

Database altered.

4) Recover database (It will issue error about the controlfile does not know about the new datafile)
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 12645623 generated at 12/12/2010 11:16:24 needed for thread 1
ORA-00289: suggestion : /u01/datafile/arch/1_43_737899751.dbf
ORA-00280: change 12645623 for thread 1 is in sequence #43

Note here the archive log file specified by Oracle is 1_43_737899751.dbf but it does not exists so the file needed is the redo log so using ls find the redo log file needed in this case is redo3b.log so it is specified when restoring
$ ls -lrt
..
-rw-r—– 1 oracle oinstall 10087936 JAN 13 22:11 1_40_737899751.dbf
-rw-r—– 1 oracle oinstall 10087936 JAN 13 23:56 1_41_737899751.dbf
-rw-r—– 1 oracle oinstall 10087936 JAN 13 11:16 1_42_737899751.dbf

$ ls -lrt redo*.log
total 1518908
-rw-r—– 1 oracle oinstall 10486272 JAN 13 23:56 redo1b.log
-rw-r—– 1 oracle oinstall 10486272 JAN 13 23:56 redo1a.log
-rw-r—– 1 oracle oinstall 10486272 JAN 13 11:16 redo2b.log
-rw-r—– 1 oracle oinstall 10486272 JAN 13 11:16 redo2a.log
-rw-r—– 1 oracle oinstall 10486272 JAN 13 19:44 redo3b.log
-rw-r—– 1 oracle oinstall 10486272 JAN 13 19:44 redo3a.log

So restoring specifying the redo log file redo3b.log

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/datafile/redo3b.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 8: '/u01/datafile/test_04.dbf’

Note the recovery didn’t start as the control file found a datafile it does not know about.

ORA-01112: media recovery not started

5) As datafile is in mount state V$datafile can be queried it shows the new datafile is called /u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008
SQL> select name from v$datafile;

NAME
——————————————————————————–
/u01/datafile/system_01.dbf
/u01/datafile/undo_01.dbf
/u01/datafile/sysaux_01.dbf
/u01/datafile/test_01.dbf
/u01/datafile/test.dbf
/u01/oradata/test_02.dbf
/u01/datafile/test_03.dbf
/u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008

6) Shows the datafile does not physical exists
$ ls -l /u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008
ls: /u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008: No such file or directory

7) Trying to recover database again it reports the filename needs to be updated in the controfile before proceeding
SQL> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 8 is unknown – rename to correct file
ORA-01110: data file 8: '/u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008'
ORA-01157: cannot identify/lock data file 8 – see DBWR trace file
ORA-01111: name for data file 8 is unknown – rename to correct file
ORA-01110: data file 8: '/u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008'

8) Create datafile from UNNAMED00008 to update controfile
SQL> alter database create datafile '/u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008' as '/u01/datafile/test_04.dbf’;

Database altered.

9) Check v$datafile after the datafile is created note the control file knows about the datafile
SQL> select name from v$datafile;

NAME
——————————————————————————–
/u01/datafile/system_01.dbf
/u01/datafile/undo_01.dbf
/u01/datafile/sysaux_01.dbf
/u01/datafile/test_01.dbf
/u01/datafile/test.dbf
/u01/oradata/test_02.dbf
/u01/datafile/test_03.dbf
/u01/datafile/test_04.dbf

10) Recover the database
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 12654692 generated at 12/12/2010 19:43:13 needed for thread 1
ORA-00289: suggestion : /u01/datafile/arch/1_43_737899751.dbf
ORA-00280: change 12654692 for thread 1 is in sequence #43

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/datafile/redo3b.log
Log applied.
Media recovery complete.

11) Open database with resetlogs
SQL> alter database open resetlogs;

Database altered.


Enjoy !!