Working with LXC containers

1. We use lxc-create command to create a new container. But it installs OS in container from the CentOS repositories available on Internet. But, to use a local repository, we can set repo environment variable for this.

# export repo="http://127.0.0.1/centos7_1503"

2. Create a container with below command, here c1 is container name.

# lxc-create -t centos -n c1

3. Commands to stop, start, and attach to console of container are

# lxc-stop -n c1
# lxc-start -n c1
# lxc-attach -n c1

4. When we stop container using lxc-stop command, it send SIGPWR signal to container. But, CentsOS 7 does not handle SIGPWR correctly to poweroff. So, here is the fix we have execute in container OS.

[root@c1 ~]# cd /usr/lib/systemd/system
[root@c1 ~]# ln -s poweroff.target sigpwr.target

5. LXC container creation script for CentOS does Minimal Install. To have a good working set of packages, configure yum and install Base group of packages.

Add following lines to file /etc/yum.repos.d/local.repo

[local]
name=local_centos7_1503
baseurl=http://192.168.2.1/centos7_1503
gpgcheck=0

Install Base group

[root@c1 ~]# yum groupinstall Base

Installing LXC from source code on a CentOS 7 host, and configuring

1. Before beginning to install LXC, the pre-requisite package libcap-devel should be installed. This assumes Yum configured on host already.

# yum install libcap-devel

2. Unzip LXC source code.

# tar -zxvf lxc-1.1.5.tar.gz
# cd lxc-1.1.5

3. Install with following commands. By default, LXC will be installed in /usr/local directory. So, we have to mention correct directory names by specifying options for ./configure command.

# ./autogen.sh
# ./configure --enable-capabilities --prefix=/usr --sysconfdir=/etc --localstatedir=/var
# make
# make install

4. Add following configuration information in /etc/sysconfig/lxc-net (new file) to configure networking for LXC host.

LXC_BRIDGE="lxcbr0"
USE_LXC_BRIDGE="true"

LXC_ADDR="192.168.2.1"
LXC_NETMASK="255.255.255.0"
LXC_DHCP_RANGE="192.168.2.70,192.168.2.99"

5. Add following line in /root/.bash_profile and /etc/init.d/lxc-net files to make LXC libraries available for startup script and environment.

# export LD_LIBRARY_PATH=/usr/lib

6. Start the service lxc-net and make it autostart during system boot.

# service lxc-net start
# chkconfig lxc-net on

7. Verify if lxcbr0 interface is showing up in ifconfig output.

# ifconfig
....
lxcbr0: flags=4163  mtu 1500
        inet 192.168.2.1  netmask 255.255.255.0  broadcast 0.0.0.0
....

8. Add following line in /etc/lxc/lxc.conf (new file) to specify where all containers' root file systems should be stored.

lxc.lxcpath = /vm

Filtering inner (null-supplying) table in outer join

Let us consider below tables to demonstrate filtering inner table in outer join

SQL> select * from t1;

         A          B
---------- ----------
         1         10
         2         20
         3         30

3 rows selected.

SQL> select * from t2;

         B          C
---------- ----------
        20        200
        30        300
        40        400

3 rows selected.

This is regular outer join where t1 is outer (row preserving) table and t2 is inner (null supplying) table.

SQL> select a, t1.b t1_b, t2.b t2_b, c from t1 left outer join t2 on t1.b = t2.b;

         A       T1_B       T2_B          C
---------- ---------- ---------- ----------
         2         20         20        200
         3         30         30        300
         1         10

3 rows selected.

Filter predicate in WHERE clause: It filters from result like below, after performing join operation.

SQL> select a, t1.b t1_b, t2.b t2_b, c from t1 left outer join t2 on t1.b = t2.b where t2.c=300;

         A       T1_B       T2_B          C
---------- ---------- ---------- ----------
         3         30         30        300

1 row selected.

Filter predicate in join condition: It filters rows from inner (null supplying) table t2, before performing join operation.

SQL> select a, t1.b t1_b, t2.b t2_b, c from t1 left outer join t2 on t1.b = t2.b and t2.c=300;

         A       T1_B       T2_B          C
---------- ---------- ---------- ----------
         3         30         30        300
         2         20
         1         10

3 rows selected.

Here are the equivalent SQLs in Oracle's dialect.

SQL> select a, t1.b t1_b, t2.b t2_b, c from t1, t2 where t1.b = t2.b(+) and t2.c=300;

         A       T1_B       T2_B          C
---------- ---------- ---------- ----------
         3         30         30        300

1 row selected.

Observe (+) in filter predicate of t2 below, which actually filters rows before join operation.

SQL> select a, t1.b t1_b, t2.b t2_b, c from t1, t2 where t1.b = t2.b(+) and t2.c(+)=300;

         A       T1_B       T2_B          C
---------- ---------- ---------- ----------
         3         30         30        300
         2         20
         1         10

3 rows selected.

Unzip multi part zip archive on Linux

This works with zip command version 3.0 or above (available in RHEL 6 onwards). Here is an example that unzips multi part zip archive of an Informatica software.

# ls -l dac_win_11g_infa_linux_64bit_951*
-rw-r--r-- 1 root root 2097152000 Nov 12  2013 dac_win_11g_infa_linux_64bit_951.z01
-rw-r--r-- 1 root root 2097152000 Nov 12  2013 dac_win_11g_infa_linux_64bit_951.z02
-rw-r--r-- 1 root root 2097152000 Nov 12  2013 dac_win_11g_infa_linux_64bit_951.z03
-rw-r--r-- 1 root root 1060733887 Nov 12  2013 dac_win_11g_infa_linux_64bit_951.zip

Note: there is a hyphen before and after 's' in below command
# zip -s- dac_win_11g_infa_linux_64bit_951.zip --out inf2.zip
 copying: 951HF2_Client_Installer_win32-x86.zip
 copying: 951HF2_Server_Installer_linux-x64.tar
 copying: DAC11gInstaller.zip
 copying: Infa951Docs.zip
 copying: Oracle_All_OS_Prod.key

# unzip inf2.zip
Archive:  inf2.zip
 extracting: 951HF2_Client_Installer_win32-x86.zip  
  inflating: 951HF2_Server_Installer_linux-x64.tar  
 extracting: DAC11gInstaller.zip     
 extracting: Infa951Docs.zip         
  inflating: Oracle_All_OS_Prod.key 

Getting all users and roles that can access objects in a schema

To list all users and roles who can access objects in a schema, we can use following query. This will be useful when there is a need of migrating a schema from one database to another.

with
users_roles as(
        (select grantee, granted_role from dba_role_privs) union
        (select username, username from dba_users) union
        (select role, role from dba_roles)
),
direct_grantees as (
        select distinct grantee 
        from dba_tab_privs 
        where owner in ('SCHEMA_USER')
),
all_grantees as (
        select distinct grantee
        from users_roles
        start with 
                granted_role in (select * from direct_grantees)
        connect by nocycle prior grantee = granted_role
)
select grantee 
from all_grantees 
-- Filter as needed
-- For example, get only users not roles
where grantee in (select username from dba_users);