Automatic Diagnostic Repository Command Interface (ADRCI)

ADRCI is a command line tool for managing Oracle’s Diagnostic data – whenever a problem is encountered in database, we diagnoise by looking at various locations like alert logs, trace files, dumps etc., It’s been a nice thing if we were able to manage all the logs at one place. ADRCI provides that kind of ability –

Let’s see the functionality of ADRCI –

To enter into ADRCI interactive mode –

C:\>adrci
ADRCI: Release 11.2.0.1.0 - Production on Sun Jun 26 19:01:09 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  
All rights reserved.
ADR base = "c:\app\mgrvinod"
adrci>

Note ::  Assuming ORACLE_HOME and PATH environment variables 
         are set properly.

Now, set ADR homepath – if you have multiple instances then you will have multiple homes, so to focus on a single ADR home, you must set the homepath to a single ADR home directory. This can be done as follows –

adrci> show homes
ADR Homes:
diag\rdbms\mgrorcl\mgrorcl
diag\rdbms\mgrorcl1\mgrorcl1
diag\rdbms\orcl\orcl
diag\tnslsnr\mypc\listener
adrci>

To set to a particular SID - 

adrci> set homepath diag\rdbms\orcl\orcl
adrci> show homes
ADR Homes:
diag\rdbms\orcl\orcl
adrci>

You can view the alert log by issuing the following command –

adrci> show alert
ADR Home = c:\app\mgrvinod\diag\rdbms\orcl\orcl:
***************************************************************
Output the results to file: c:\..\alert_10932_10912_orcl_8.ado

You can even limit to view only the ORA - errors in the 
alert log - 

adrci> SHOW ALERT -P "MESSAGE_TEXT LIKE '%ORA-%'"

ADR Home = c:\app\mgrvinod\diag\rdbms\orcl\orcl:
***************************************************************
Output the results to file: c:\..\alert_10932_10912_orcl_9.ado

For rectifying any problem, sometimes it is necessary to contact Oracle support. Before doing so, it is necessary to gather relevant information, which will be useful for problem study. ADRCI utility is handy in not only providing the relevant information like trace files, alert logs, incident dumps etc., but it is also useful in grouping all the relevant file into a single package(compressed file).

Let us see how this can be done –

 
adrci> show incidents
ADR Home = c:\app\mgrvinod\diag\rdbms\orcl\orcl:
**************************************************************
INCIDENT_ID   PROBLEM_KEY   CREATE_TIME
-------------------- -----------------------------------------
161           ORA 227       2011-05-25 18:20:46.735000 +08:00
162           ORA 227       2011-05-25 18:21:49.522000 +08:00
2 rows fetched

Oneo can get more detailed information regarding the above incidents
by issuing the following command ..

adrci> show incident -mode DETAIL -p "incident_id=161"      

ADR Home = c:\app\mgrvinod\diag\rdbms\orcl\orcl:
**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
   INCIDENT_ID              161
   STATUS                   ready
   CREATE_TIME              2011-05-25 18:20:46.735000 +08:00
   .
   .
   .
   PROBLEM_KEY              ORA 227
   FIRST_INCIDENT           161
   FIRSTINC_TIME            2011-05-25 18:20:46.735000 +08:00
   LAST_INCIDENT            162
   LASTINC_TIME             2011-05-25 18:21:49.522000 +08:00
   .
   .
   OWNER_ID                 1
   INCIDENT_FILE            c:\app\mgrvinod\diag\rdbms\orcl
                              \orcl\trace\orcl_m000_416.trc
   OWNER_ID                 1
   INCIDENT_FILE            c:\app\mgrvinod\diag\rdbms\orcl
                               \orcl\incident\incdir_161
                               \orcl_m000_416_i161.trc
1 rows fetched

Packages can be created based on Incident ID’s or Problem keys or also
using time. So with the help of Incident Packaging Service (IPS)
we can create the packages –

adrci> IPS CREATE PACKAGE INCIDENT 161
Created package 1 based on incident id 161,correlation level typical
adrci>

This creates a package and includes diagnostic information for 
incident(s). 

This package is a logical construct only, which means a collection 
of metadata in the Automatic Diagnostic Repository (ADR). As you add 
and remove package contents, only the metadata is modified. When you 
are ready to upload the data to Oracle Support,you create a physical 
package using ADRCI, which saves the data into a zip file. 

You can add/remove new incidents or files to the created package - 

adrci> IPS ADD INCIDENT 162 PACKAGE 1
Added incident 162 to package 1
adrci>

adrci> IPS ADD FILE c:\app\..\orcl_m000_416_i161.trc PACKAGE 1
Added file c:\app..\orcl_m000_416_i161.trc to package 1
adrci>

adrci> ips remove file c:\..\alert_orcl.log package 1;
Removed file c:\..\alert_orcl.log from package 1
adrci>

Now we are ready to generate a Physical package – This is a compressed file created in the current directory or else you can mention the location in the command –

adrci> ips generate package 1;
Generated package 1 in file C:\ORA227_20110626001721_COM_4.zip, 
mode complete
adrci>

Why should we configure limits.conf for 

Oracle database?

Hey guys, today i have read lot of good articles and feel like sharing 

with you for knowledge sake.

So this is one of the interesting articles I have read that earlier
I never knew that this

file carries this much importance and its really worth reading..!

So, these configurations will have direct impact on Oracle Database Performance, 

understanding these configurations will give you a clear picture to address 

 any “Performance Tuning” issues going forward.

limits.conf:

I have taken the below example from the installation documentation of Oracle 12cr1.

oracle   soft   nofile    1024

oracle   hard   nofile    65536

oracle   soft   nproc    16384

oracle   hard   nproc    16384

oracle   soft   stack    10240

oracle   hard   stack    32768

There are 4 columns in this syntax, let us understand these columns wise first

 and then read it as a line.

Column 1: It specifies to which user on OS, the limits are applicable.
As Oracle guides us to create user “oracle” on UNIX, it guides to
use “oracle” in the first column. If you are using any different
user to install Oracle software, then please do include that username. 

For example, include user “grid” for Oracle Grid installation.

Column 2: It accepts two options “soft” and “hard”. “soft” will be
 the maximum number that a user can set. “hard” tells the maximum
number that a user(oracle) can re-configure. 

Confusing? We will pick an example later to understand the same.

Column 3: On what resource the limits are applied. In the configuration 

we see three resource names: “nofile”,”nproc”,”stack”(New in 12c).

“nofile” specifies maximum number of file handlers that oracle

user can have at any given point in time. “nproc”  specifies maximum 

number of processes that oracle user can run on the

server. “stack” specifies maximum stack size in KB for

each thread that oracle user

process creates on the server.

Column 4: Specifies the maximum number associated to its resource parameters explained
in column 3 above.

Let us read the lines now.

Line 1:

oracle   soft   nofile    1024

Specifies the maximum number of 1024 file handlers are allowed to the user oracle on

 the server.

Line 2:

oracle   hard   nofile    65536

If oracle user reaches the bottleneck of 1024 as specified in line 1 and would wish to

 extend his limit, he can resize it to maximum of 65536. Beyond which he will need root

 access to extend. Which means that you can extend you soft limit as oracle user only until 65536 without root login.

Line 3:

oracle   soft   nproc    16384

Specifies the maximum number of 16384 processes are allowed to run by the user

 oracle on the server.

Line 4:

oracle   hard   nproc    16384

As hard limit value and soft limit value for resource nproc is same, user oracle cannot 

extend this value beyond 16384 through his login. Resizing this will need root login.

Line 5:

oracle   soft   stack    10240

This specifies the maximum stack size of 10MB to each process thread that oracle user 

handles.

Line 6:

oracle   hard   stack    32768

Specifies that if user oracle wants to extend the size of this resource, he can resize it to

 maximum of 32 MB.

Well, hope it is fine so far in understanding what these configurations mean to Oracle 

Database software installation.

Case study:

Let us test the same by changing one of the resources “nproc” on Oracle 12c Database 

running on Oracle Enterprise Linux.

1. Connected to the server as oracle and checked my resource limits.

cat /etc/security/limits.conf

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

oracle soft stack 10240

oracle hard stack 32768

So, the nproc soft limit is 2047 and hard limit is 16384

2. Started oracle database to check how many maximum processes it is creating to bring 

up the instance.

. oraenv

[oracle@ORASRV1 ~]$ . oraenv

ORACLE_SID = [oracle] ? ORADB1

The Oracle base has been set to /oracle/app/oracle

[oracle@ORASRV1 ~]$ sqlplus / as sysdba

SQL> startup;

ORACLE instance started.

Total System Global Area  834666496 bytes

Fixed Size                  2929888 bytes

Variable Size             599788320 bytes

Database Buffers          226492416 bytes

Redo Buffers                5455872 bytes

Database mounted.

Database opened.

SQL> exit

3. The number of process in my case found to be 61(It includes database software processes

 and shell processes as well).

[oracle@ORASRV1 ~]$ ps -ef | grep oracle | wc -l

61

4. Brought down Oracle database instance now.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>exit

5. Now changed soft limit of nproc for oracle user to just 30.

vi /etc/security/limits.conf

oracle soft nproc 30

…

6. Restarted server – Not mandatory, we can even reconnect in another putty session to 

apply changes.

7. Now connected as oracle user and started the database.

[oracle@ORASRV1 ~]$ sqlplus / as sysdba

SQL> startup;

ORACLE instance started.

Total System Global Area  834666496 bytes

Fixed Size                  2929888 bytes

Variable Size             599788320 bytes

Database Buffers          226492416 bytes

Redo Buffers                5455872 bytes

Database mounted.

Database opened.

SQL> exit

8. Do you think everything is working fine? NO, check that your shell is now broken. 

You will not be able to run anything in your terminal.

[oracle@ORASRV1 ~]$ ps -ef | grep oracle | wc -l

-bash: fork: retry: No child processes

-bash: fork: retry: No child processes

-bash: fork: retry: No child processes

-bash: fork: retry: No child processes

-bash: fork: Resource temporarily unavailable

9. Re-check the same by connecting a new putty session as different user. 

In my case I logged in as “root” user.

[root@ORASRV1 ~]# ps -ef | grep oracle | wc -l

29

10. There are only 29 processes that Linux kernel allowed to oracle user to 

create as we have set soft limit to 30. This has intense effect on the software.

 It will not even allow you to establish a new terminal on putty as the maximum 

process count reached.

Impact on performance :

One of the examples that i could think about at the moment related to this blog is “stack”

 where the max size per thread is 10MB, if a server process tries to access block size of more

 than 10MB size, then you would see performance issue in the database. You can relate the 

same to the block size in the database.

If we do not configure any resource limits, then no user on the server have any limitations. 

You will be free to use any amount of resources.

If you have a confusion on file handler :

When a process starts running on the server, it might try accessing files on the disk. 

Can be once or many times based on the program that process runs. 

The resource limit says the maximum number of file handles that ORACLE user on 

the OS can have at any given point of time. Irrespective of no of processes

1. What Is Fast Recovery Area?

1.What Is Fast Recovery Area?
2. Who (What) Is Stored In The Fast Recovery Area?
3. How To Enable The Fast Recovery Area?
4. Bonus: Where To Check For More Information?

Fast Recovery Area  is a centralized storage location for backup and recovery related files.
The FRA concept was introduced in 10g. 

2. Who (What) Is Stored In The Fast Recovery Area?

FRA stores 2 types of files: permanent files and transient files.

A permanent file is a file that will never be deleted by Oracle, and whos life will never become obsolete. Multiplexed control files and redo logs belong to this category.
A transient file is a file that eventually will be deleted by Oracle. Archived logs, flashback logs, RMAN backups belong to this category.

If you plan on storing all these files in the FRA, then you need to ensure there is enough space for the following:

• a copy of all the datafiles
• incremental backups (depending on your backup strategy)
• archived logs
• online redo logs
• control files
• backups of the controlfiles
• flashback logs if applicable.

Depending on your database size, the FRA size can get pretty big.

This brings me to ask the question: What happens when the FRA is full? What happens when a backup fails because there is not enough free space in the FRA?
You might be tempted to say “Not a big deal!”, but just think about it for a minute…
If the archiver process cannot write archived logs to disk, what happens to the database? The database hangs, people cannot login, an outage just got created!

Please note, I am not saying not to implement the FRA! I am just bringing awareness of possible challenges the FRA could create.

To implement the FRA properly and not come across these type of problems, more thought needs to be put into the process, and some extra setup is required.

3. How To Enable The Fast Recovery Area?

There are 2 init.ora parameters that need to be set, in order to setup the FRA. What is interesting about this, and it makes sense, is the fact that you need to set the parameters in a specific order.

To enable the FRA, set these parameters:

1. First you must set the size of FRA: db_recovery_file_dest_size
2. Second set the location of FRA: db_recovery_file_dest

Both parameters can be set with an alter system command:

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 100G SCOPE=BOTH SID='*';
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u01/oradata/backups' SCOPE=BOTH SID='*';

4. Bonus: Where To Check For More Information?

There are 2 views that will be useful for you to query:

v$recovery_file_dest – disk quota and current disk usage in the FRA.
v$flash_recovery_area_usage – usage information about FRA.
v$recovery_area_usage – usage information about FRA.

Increase the size of a Linux LVM by expanding the virtual machine disk

Many time we need additional space on Linux OS which is created on VMBox so “How to Increase the size of a Linux LVM by expanding the virtual machine disk” , this article will definitely help you out if you have getting error for space.

Note : This is for “Fixed” sized VDI created in VMBox.

Step 1: Goto path where you have installed VMBox here my path is “C:\Program Files\Oracle\VirtualBox\VirtualBox-4.3.22-98236-Win” and clone (or we can say take backup) present VDI.

Command: 

C:\Program Files\Oracle\VirtualBox>vBoxManage clonehd "C:\Users\kaatchi\VirtualBox VMs\12C\12C.vhd" "C:\Users\kaatchi\VirtualBox VMs\12C\12C_clone.vhd" -format VDI -variant Standard

0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%

Clone medium created in format 'VDI'. UUID: df77ed6b-5d2c-4569-badb-5968ce6d636c

Note: Above command will take some time to complete.

Step 2: You have to now modify the cloned VDI to different size from below image you
can see that I have made my Hardisk VDI to 50GB which was before 30GB.

Command:

C:\Program Files\Oracle\VirtualBox>vBoxManage modifyhd "C:\Users\kaatchi\VirtualBox VMs\12C\12C_clone.vhd" --resize 51200

0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%

Step 3: Now again clone the modified VDI as below.

Command: 

C:\Program Files\Oracle\VirtualBox>vBoxManage clonehd "C:\Users\kaatchi\VirtualBox VMs\12C\12C_clone.vhd" "C:\Users\kaatchi\VirtualBox VMs\12C\12C1.vhd" -format VDI -
variant Fixed

Attached the new VDI to VMBox and start the VMBox, mine is node1.vdi so I  have attached this.

Congratulations, you have Increased the size of hardisk VDI on VMBox, you can check it in VMBox machine settings as below:

Step 4: This is not the end , the question remain the same “how to increase the size of a Linux LVM”, for this login to Linux OS by root user and you to increase the size by fdisk command as below:

[root@rac1 ~]# fdisk -l

Disk /dev/sda: 42.9 GB, 42949672960 bytes
255 heads, 63 sectors/track, 5221 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00058335

Device Boot Start End Blocks Id System
/dev/sda1 * 1 64 512000 83 Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2 64 2611 20458496 8e Linux LVM
/dev/sda3 2611 3916 10483750 8e Linux LVM
/dev/sda4 3917 5221 10482412+ 8e Linux LVM

Disk /dev/sdb: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xc0e1c9cb

Device Boot Start End Blocks Id System
/dev/sdb1 1 1305 10482381 83 Linux

Disk /dev/sdc: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x3357c574

Device Boot Start End Blocks Id System
/dev/sdc1 1 1305 10482381 83 Linux

Disk /dev/sdd: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x63ef4291

Device Boot Start End Blocks Id System
/dev/sdd1 1 1305 10482381 83 Linux

Disk /dev/mapper/vg_rac1-lv_root: 29.5 GB, 29532094464 bytes
255 heads, 63 sectors/track, 3590 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

Disk /dev/mapper/vg_rac1-lv_swap: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

From above fdisk -l output  /dev/sda4  is listed as “Linux LVM” and it has the ID of 8e, this is hex code which tells that it is a Linux LVM.

Note: Below /dev/mapper/vg_rac1-lv_root is the volume made for /dev/sda4  and this is what we will be expanding.

[root@rac1 oracle]# df -h
Filesystem                   Size  Used Avail Use% Mounted on
/dev/mapper/vg_rac1-lv_root   27G   20G  6.6G  75% /
tmpfs                        2.3G   72K  2.3G   1% /dev/shm
/dev/sda1                    477M   85M  363M  19% /boot
Softwares                    373G  331G   42G  89% /media/sf_Softwares

Now, start following below steps for adding partition:

[root@rac1 ~]#

[root@rac1 ~]# fdisk /dev/sda
WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Selected partition 4
First cylinder (3917-5221, default 3917):
Using default value 3917
Last cylinder, +cylinders or +size{K,M,G} (3917-5221, default 5221):
Using default value 5221

Command (m for help): t
Partition number (1-4): 4
Hex code (type L to list codes): 8e
Changed system type of partition 4 to 8e (Linux LVM)

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.

WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table. The new table will be used at
the next reboot or after you run partprobe(8) or kpartx(8)
Syncing disks.

Now we have to create a physical volume which later use by the logical volume manager (LVM)

[root@rac1 ~]# pvcreate /dev/sda4
  Device /dev/sda4 not found (or ignored by filtering).

Above  is showing error to create , so as a solution you have to reboot the system.

[root@rac1 ~]#
[root@rac1 ~]# reboot
[root@rac1 ~]#
Broadcast message from root@rac1.localdomain
        (/dev/pts/0) at 14:47 ...

The system is going down for reboot NOW!
login as: root
root@192.168.56.71's password:
Last login: Tue Jun  6 14:45:09 2017 from 192.168.56.1
[root@rac1 ~]#
[root@rac1 ~]#

After reboot again execute the command.

[root@rac1 ~]#  pvcreate /dev/sda4
  Physical volume "/dev/sda4" successfully created

Next  we have to check the name of the current volume group using the vgdisplay command. The name will be different on your system here the name is “vg_rac1” for me.

[root@rac1 ~]# vgdisplay
  --- Volume group ---
  VG Name               vg_rac1
  System ID
  Format                lvm2
  Metadata Areas        2
  Metadata Sequence No  5
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                2
  Open LV               2
  Max PV                0
  Cur PV                2
  Act PV                2
  VG Size               29.50 GiB
  PE Size               4.00 MiB
  Total PE              7553
  Alloc PE / Size       7553 / 29.50 GiB
  Free  PE / Size       0 / 0
  VG UUID               rV6Zxc-W8xS-vVOK-M6iV-WniH-leB1-RCTi59

Now extent the size as below:

[root@rac1 ~]# vgextend vg_rac1 /dev/sda4
  Volume group "vg_rac1" successfully extended

Next execute pvscan command which has to scan all disks for physical volumes, this should display new added /dev/sda4

[root@rac1 ~]#
[root@rac1 ~]# pvscan
  PV /dev/sda2   VG vg_rac1   lvm2 [19.51 GiB / 0    free]
  PV /dev/sda3   VG vg_rac1   lvm2 [10.00 GiB / 0    free]
  PV /dev/sda4   VG vg_rac1   lvm2 [9.99 GiB / 9.99 GiB free]
  Total: 3 [39.50 GiB] / in use: 3 [39.50 GiB] / in no VG: 0 [0   ]

Now check the path of the logical volume using lvdisplay:

[root@rac1 ~]# lvdisplay
  --- Logical volume ---
  LV Path                /dev/vg_rac1/lv_root
  LV Name                lv_root
  VG Name                vg_rac1
  LV UUID                e2eZMR-lFDh-eCB0-i84w-SMlJ-X59Z-hWfljC
  LV Write Access        read/write
  LV Creation host, time localhost.localdomain, 2017-05-10 04:25:50 +0530
  LV Status              available
  # open                 1
  LV Size                27.50 GiB
  Current LE             7041
  Segments               2
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     256
  Block device           252:0

  --- Logical volume ---
  LV Path                /dev/vg_rac1/lv_swap
  LV Name                lv_swap
  VG Name                vg_rac1
  LV UUID                Z4cghb-HLNX-wkp2-0tIM-20dO-M52K-f1mwdJ
  LV Write Access        read/write
  LV Creation host, time localhost.localdomain, 2017-05-10 04:25:54 +0530
  LV Status              available
  # open                 2
  LV Size                2.00 GiB
  Current LE             512
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     256
  Block device           252:1

Now extend it:

[root@rac1 ~]# lvextend /dev/vg_rac1/lv_root /dev/sda4
  Extending logical volume lv_root to 37.50 GiB
  Logical volume lv_root successfully resized

Finally but very important executed below command which is to resize the file system:

[root@rac1 ~]# resize2fs /dev/vg_rac1/lv_root
resize2fs 1.43-WIP (20-Jun-2013)
Filesystem at /dev/vg_rac1/lv_root is mounted on /; on-line resizing required
old_desc_blocks = 2, new_desc_blocks = 3
The filesystem on /dev/vg_rac1/lv_root is now 9829376 blocks long.

Congratualations, Your added space is ready it has increased to 16GB now

[root@rac1 ~]# df -h
Filesystem                   Size  Used Avail Use% Mounted on
/dev/mapper/vg_rac1-lv_root   37G   20G   16G  55% /
tmpfs                        2.3G  138M  2.2G   6% /dev/shm
/dev/sda1                    477M   85M  363M  19% /boot
Softwares                    373G  331G   42G  89% /media/sf_Softwares