FlashBack Archive run out of space

Error while executing any DML statements

SQL> update employees set salary=salary*1.1;
update employees set salary=salary*1.1
       *
ERROR at line 1:
ORA-55617: Flashback Archive "FLA1" runs out of space and tracking on
"EMPLOYEES" is suspended

The is because flashback archive has reached its limit, ORA-55617 happens because tablespace has reached it max size. Query the name of the tablespace used for flashback archive and its quota.

SQL> select * from dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NA FLASHBACK_ARCHIVE# TABLESPACE_NAME         QUOTA_IN_MB
-------------------- ------------------ ------------------------------ -----------
FDA1          1 FDA_TBS1         100
FLA1          2 FLA_TBS1         10

SQL> 

Now it is clear that Flashback archive FLA1 has 10MB quota on tablespace FLA_TBS1. Verify the size of tablespace and resize the datafile before heading ahead. Find the size of the tablespace.

SQL> select tablespace_name,bytes/1024/1024 as "Size MB",maxbytes/1024/1024 as "MaxSize MB" 
from dba_data_files where tablespace_name='FLA_TBS1';

TABLESPACE_NAME     Size MB MaxSize MB
------------------------------ ---------- ----------
FLA_TBS1         10     0

Find the datafile of tablespace FLA_TBS1.

SQL> select t.name,d.name
  2  from v$tablespace t join v$datafile d
  3  using (ts#)
  4  where t.name='FLA_TBS1';

NAME
------------------------------
NAME
--------------------------------------------------------------------------------
FLA_TBS1
/home/oracle/BACKUP/fla_tbs01.dbf

Resize datafile with alter database command.

SQL> alter database datafile '/home/oracle/BACKUP/fla_tbs01.dbf' resize 1000m;

Database altered.

SQL> 

Verify the size of the tablespace now.

SQL> select tablespace_name,bytes/1024/1024 as "Size MB",maxbytes/1024/1024 as "MaxSize MB" 
from dba_data_files where tablespace_name='FLA_TBS1';

TABLESPACE_NAME     Size MB MaxSize MB
------------------------------ ---------- ----------
FLA_TBS1        1000    0

SQL>

Increase the quota of the flashback archive fla1 to 50M

SQL> alter flashback archive fla1
  2  modify tablespace FLA_TBS1 quota 50m;

Flashback archive altered.

Now Any DML command will execute successfully till the quota exceed.

SQL> update employees
  2  set salary=salary*1.1;

107 rows updated.

SQL> 

Read More

Linux Enable user with blank password.

Create user on linux.

sudo useradd user1

By default user is created and is disabled for login till we set the new password. Execute following command to verify and clear the password from linux user user1.

sudo grep user1 /etc/shadow

sudo passwd -d user1

By default ssh is not allowed to login with blank password. We need to enable it with following parameter.

PermitEmptyPasswords yes

Restart ssh service to reflect the change.

/etc/init.d/sshd restart

Now you may login without password prompt.

ssh user1@10.10.10.250

Removing password from user and allowing Empty password to login via ssh is great threat. Enable it at your own risk and do not configure it unless it is required and understood properly.

Read More

Secure Linux server 3

Keep /boot as read-only

/boot contains the kernel, ramdisk images as well as bootloader configuration file and bootloader stages. This partition is not required for normal system operation, but read is required while boot and read and write required while kernel upgrades. It will be safer to mount this partition as read only on production system. We can remount this partition with read write whenever kernel upgrades are required.

By default /boot is mount with read and write option while installation. We can modify /etc/fstab to change the mount option. Execute mount command to view all the partitions mounted.

mount

Modify fstab entry so that boot partition will be mounted read only.

vi /etc/fstab
replace default with default,ro on mount entry.

Remount /boot partition with read only option and verify that boot partition is mount to read only mode.

sudo mount -o remount,ro /boot

mount

Temporary remount /boot on read write mode whenever required(in case of kernel upgrade or boot option changes).

sudo mount -o remount,rw /boot

Mount /tmp and data partition With nodev, nosuid, and noexec Options

By default /tmp directory will be accessible to everyone. That is the reason most of hacker and crackers use /tmp as the storage area to store the malicious code and execute them. We can mount /tmp with nodev, nosuid and noexec to avoid such attempts.

Any user can create file and execute it as below.

cd /tmp/

cat > hello.sh

echo "This is hello message from tmp"

^C

chmod 755 hello.sh 

/tmp/hello.sh 

nosuid: Do not set SUID/SGID access on this partition.

nodev: Do not set character or special devices access on this partition.

noexec: Do not allow direct execution of any binaries on the mounted filesystem.

/dev/sdb1               /tmp                    ext4    defaults,nosuid,nodev,noexec       0 0

noexec can be used on the partition with datafile and execution of the binary file is not required. Lets say we can mount data partition of database with noexec Option. In the case below executable file created on /tmp is not directly executed. We can use sh to execute the binary file.

Ignore ICMP or Broadcast Request

ICMP packets are used to verify Network connectivity. Sometime it will be used in indirect ICMP flooding, also known as smurfing resulting DoS (Denial of Service) attack. To prevent Linux server from such attack we need to modify some kernel parameters. Add following kernel parameters to /etc/sysctl.conf file.

net.ipv4.icmp_echo_ignore_all = 1
net.ipv4.icmp_echo_ignore_broadcasts = 1

Execute sysctl to configure kernel parameters at runtime.

sysctl -p

Implement denyhost to Ban Malicious IP Addresses

Configure denyhost on Linux

Avoid Using FTP, Telnet, And Rlogin / Rsh Services

Verify these services are not running with ps ax command. If any of the services are running and are not required then disable it or remove it.

ps ax | grep -i 'ftp\|telnet\|rsh\|rlogin'

Use sudo instead of root access direct.

sudo(su do) permits user to execute some or all command as super users. Though we can directly modify /etc/sudoers file it is recommended to modify sudo settings with visudo command. 

Alias represent the groups and we have following alias. Host Alias specifies the host names the cmd_alias is valid for. Unless you are sharing a sudoers file among different hosts, this alias does not comes in proper use. Use ALL or  hostname of the server or IP address of the server where sudoers file is located if sudoers file is not shared.

Host Aliases (Also known as host list)

Host_Alias DCLAN = 192.168.0.1/28: DBSERVERS = 192.168.1.250, mylinux

User Aliases (Also known as user list)

User_Alias ADMINS = dilli : BCKADMIN = raju, kamal

Command Aliases (Also known as command list)

Cmnd_Alias CMD_ADMIN = /sbin/poweroff : CMD_BCKADMIN = /bin/rsync : CMD_ORCLBCK = /home/oracle/rman

Runas_Alias (Also known as Operator list)

Runas_Alias RLIST1 = oracle

tag list

Tag_Spec ::= (NOPASSWD: | PASSWD: | NOEXEC: | EXEC: |

                   SETENV: | NOSETENV: | LOG_INPUT: | NOLOG_INPUT: |

                   LOG_OUTPUT: | NOLOG_OUTPUT:)

User Specifications are where the sudoers file sets who can run what as who.
syntax:

ADMINS ALL=(ALL) NOPASSWD:CMD_ADMIN

All user listed on the ADMINS User_Alias are allowed to execute command listed on CMD_ADMIN alias on any server with privileges of any user. In addition to that password will not be prompted

when executing the command listed on the command alias CMD_ADMIN.

sudo /sbin/poweroff

User dilli can execute sudo /sbin/poweroff without password prompt.

BCKADMIN DBSERVERS=(RLIST1) PASSWD:CMD_ORCLBCK

User listed on User Alias BCKADMIN that is raju,kamal are allowed to execute command listed on CMD_ORCLBCK command alias with password prompt for 

hosts defined on the host alias DBA. Users are allowed to run command as user oracle only. Host configuration works if the sudoers file is shared among the servers. Otherwise ALL or hostname of the server where sudoers are applied can be used.

sudo  /home/oracle/rman

This command failed with user raju is not allowed to execute command as user root. 

In this case we have to use option -u and specify the user.
sudo -u oracle /home/oracle/rman

Read More

Purge MySQL binary logs

The binary log is a set of files that contain information about data modifications made by the MySQL server. These files are generated on master server while bin log is enabled. Over the time the number of those bin log files grows and need to clean on regular basic. Purge binary log will delete binary logs on basic of the give values so It is recommended to backup those binary log files into tape or external drives for future.

We can purge binary on basic of log_name or date before expression.

PURGE BINARY LOGS
    { TO 'log_name' | BEFORE datetime_expr };

PURGE BINARY LOGS TO 'master-bin.010';

Deletes all binary log that are created before master-bin.010.

PURGE BINARY LOGS BEFORE '2016-01-01 00:00:00';
Deletes all binary log that are created before January 1st 2016.

As shown on figure below I have around 10% space free.

More than 52 binary log files are created. I need to purge them.

Execute purge command so that it will be deleted from OS and from MySQL metadata.

Purge binary log to 'master-bin.000400';

Verify the files are deleted from OS side.

Read More