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 !!

Oracle Backup - 1

Oracle Database Backup RMAN

1 day in the morning I was facing ORA-00245 and ORA-27037 during RMAN of (RAC with ASM) backup in office. We have license of Oracle Enterprise Edition of RAC. The surprising thing was getting ORA-00245 on node 1 and ORA-27037 on node 2 !!

Here,

ORA-00245: control file backup failed; target is likely on a local file system

ORA-27037: unable to obtain file status

So checked the lines above this ORA error, pointing me to some trace files. But was not able to find anything. I checked that ORA-27037 pointing me to get status of snapshot contorlfile on node 2 of node 1. Means snapshot controlfile of node 1 was required at node 2 !!

How it's possible? Checked inside metalink and got the architectural help which is obvious.

For RAC database, due to the changes made to the controlfile backup mechanism in 11gR2, any instance in the cluster may write to the snapshot controlfile. Due to this snapshot controlfile need to be visible to all instances. 

The snapshot controlfile MUST be accessible by all nodes of a RAC database, if the snapshot controlfile does not reside on a shared device error will be raised at the time of RMAN backup while taking snapshot of controlfile.

So, Follow below steps to your RAC environment.

RMAN> show snapshot controlfile name;

If you are not sing share storage ASM and using shared file system then use below command.

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '<shared_disk>/snapcf_<DBNAME>.f';

or if you are using ASM then got for below

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+<DiskGroup>/snapcf_<DBNAME>.f';

This will now pointing to same shared location and will not give you backup related error.

Enjoy!!