Create disk partitions and disk lables for ASM instance

Creating ASM disks and adding to existing diskgroups

As a root user, check for available disks :

fdisk -l
Disk /dev/vdb: 107.4 GB, 107374182400 bytes
16 heads, 63 sectors/track, 208050 cylinders
Units = cylinders of 1008 * 512 = 516096 bytes

Create new partitions

fdisk /dev/vdb
Command (m for help): p
Command (m for help): n
Command action
 e extended
 p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-208050, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-208050, default 208050): +30G
Command (m for help): p
Command (m for help): n
Command action
 e extended
 p primary partition (1-4)
p
Partition number (1-4): 2
First cylinder (31-208050, default 31):
Using default value 31
Last cylinder, +cylinders or +size{K,M,G} (31-208050, default 208050): +30G
Command (m for help): w
The partition table has been altered!

Install following rpms : Read More

Cloning Oracle home and creating a new database

Oracle database Installation using binaries copied from other server (Perl clone on RHEL/Centos):

1. Make sure following rpms are installed :

binutils 
compat-libcap1 
compat-libstdc++-33
gcc 
gcc-c++ 
glibc 
glibc-devel 
ksh 
libgcc 
libstdc++ 
libstdc++-devel 
libaio 
libaio-devel 
make 
sysstat

2. Create a new user and group who will be the owner of oracle binaries.

groupadd dba
useradd -g dba oracle --> -g to add dba as primary group

3. Check ulimits (/etc/security/limits.conf) Read More

Oracle data dictionary view does not exist error

Error while accessing catalog view DBA_DATA_FILES.

SQL> desc DBA_DATA_FILES
ERROR:
ORA-04043: object DBA_DATA_FILES does not exist
SQL> desc DBA_segments
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 SEGMENT_NAME                                       VARCHAR2(81)
 PARTITION_NAME                                     VARCHAR2(30)
 SEGMENT_TYPE                                       VARCHAR2(18)
 SEGMENT_SUBTYPE                                    VARCHAR2(10)
 TABLESPACE_NAME                                    VARCHAR2(30)
 HEADER_FILE                                        NUMBER
 HEADER_BLOCK                                       NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 EXTENTS                                            NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                                        NUMBER
 MAX_EXTENTS                                        NUMBER
 MAX_SIZE                                           NUMBER
 RETENTION                                          VARCHAR2(7)
 MINRETENTION                                       NUMBER
 PCT_INCREASE                                       NUMBER
 FREELISTS                                          NUMBER
 FREELIST_GROUPS                                    NUMBER
 RELATIVE_FNO                                       NUMBER
 BUFFER_POOL                                        VARCHAR2(7)
 FLASH_CACHE                                        VARCHAR2(7)
 CELL_FLASH_CACHE                                   VARCHAR2(7)

While we are able to access all other dictionary views, unable to access only dba_data_files .

FIX :

Read More

xtrabackup fails with error number 23

innobackupex fails with below error:

InnoDB: Operating system error number 23 in a file operation.
InnoDB: Error number 23 means 'Too many open files in system'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
InnoDB: Error: could not open single-table tablespace file

Issue :

Innobackupex is failing due to server restriction on open file descriptors for ‘mysql’ user.

[email protected][/mysql/data] ulimit -Hn
32000

Ulimit hard limit is only set to 32k, we can estimate the ulimit required by Mysql user by looking at the number of partitions/tables we have.

[email protected][/mysql/data] find . -name "*.ibd"|wc -l
185293

Or

mysql> select count(*) from information_schema.partitions;
+----------+
| count(*) |
+----------+
|   185433 |
+----------+
1 row in set (1.68 sec)

Fix :

Read More