Monthly Archives: August 2013

2013-08-27
Leave a comment
Information Security, Technology

Information Security Hardening Best Practices

  • Keep all systems updated, especially your front-end. The only reason delaying update is only version compatibility. Keep everything simple to let them keep updated to latest patch. Automate!
  • Limit outbound access and don’t let malicious go away to the Internet that easy, they may get in, but no way out.
  • Regular systems scanning.
  • Intrusion detection systems for some noobs.
  • Always beware of social engineering.
  • Never open any suspicious link. Test suspicious link/email only at isolated machine. Permanently delete them as much as you can.
  • …
    • Several basic points to improve your database security:
    1. Limit DBA team access only from localhost.
    2. Limit application account privilege to lowest possible.
    3. Limit access to database from necessary entries. Database is always behind application, internal only.
    4. Have some monitoring on both logs and resource usage. SQL injection usually increases resource usage.
    5. Encrypt data communication and backups.
    6. …
    Several URLs:  ]]>

    Tagged
    2013-08-23
    Leave a comment
    Linux, Technology

    Vmware error Device eth does not seem to be present

    ls /sys/class/net Remove the kernel’s networking interface rules file: 

    # rm -f /etc/udev/rules.d/70-persistent-net.rules
    Restart: 
    # reboot
    ]]>

    2013-08-23
    Leave a comment
    Linux, Technology

    NFS ports

  • From /etc/services NFS basically uses 2 ports: 111 (sunrpc) and 2049 (nfs)
  • From /etc/sysconfig/nfs get all the default ports there: 875,32803,32769,892,662,2020,20049
    • How to check NFS:
    • showmount -e x.x.x.x
    • cd /net/x.x.x.x/folder
    Related config:
    • /etc/exports
    ]]>

    Tagged ,
    2013-08-23
    Leave a comment
    Linux, Technology, Windows

    Remote desktop to Linux from Windows

    yum -y install vnc vnc-server Add normal user: 

    #adduser user1
#sudo su - user1
#vncpasswd
    If you want to add root 
    #vncpasswd
    Edit your config: 
    #vi /etc/sysconfig/vncservers
VNCSERVERS="1:root 2:user1"
VNCSERVERARGS[1]="-geometry 800x600"
VNCSERVERARGS[2]="-geometry 800x600"
    Iptables, add more multiple destination ports if required, example: 
    iptables -I INPUT  -p tcp -m multiport --dports 5901:5903,6001:6003 -j ACCEPT
    To restart: 
    # service vncserver restart
# chkconfig vncserver on
    If you want to secure more, block those ports and use ssh to do tunneling inside the vnc server. On your Windows, use tightvnc or realvnc. Toubleshooting:
    1. bad hostname. Make sure you have hostname correctly. If you just install a server, non-DNS, put your hostname after 127.0.0.1, example: 127.0.0.1 hostname
    2. On your client if it’s refused, do use port. VNC uses 5901 for terminal 1, 5902 for terminal 2, etc. Therefore your connection should be: x.x.x.x:5901 or x.x.x.x:5902
    ]]>

    Tagged , ,
    2013-08-22
    Leave a comment
    Oracle, Technology

    Oracle tip #2: check tablespace and usage

    SELECT a.tablespace_name, round(a.bytes / 1048576) allocated_mb, round(b.free_bytes / 1048576) free_mb, ROUND(((b.free_bytes / 1048576) * 100) / (a.bytes / 1048576), 2) PERCENT_FREE FROM dba_data_files a, (SELECT file_id, SUM(bytes) free_bytes FROM dba_free_space b GROUP BY file_id) b WHERE a.file_id=b.file_id ORDER BY a.tablespace_name; 

    TABLESPACE_NAME ALLOCATED_MB FREE_MB PERCENT_FREE
------------------------------ ------------ ---------- ------------
ACME_DATA 100 99 98.88
ACME_INDEX 100 99 98.94
EXAMPLE 100 21 21.25
RMAN_DATA 100 93 93.44
SYSAUX 820 56 6.78
SYSTEM 740 7 .96
UNDOTBS1 360 344 95.49
USERS 5 1 17.5
    ]]>

    Tagged ,
    2013-08-22
    Leave a comment
    Oracle, Technology

    Oracle tip #1: make it friendly

    #vi .bash_profile export SQLPATH=$HOME/scripts export PS1='[\u@\h:${ORACLE_SID}]$ ‘ [oracle@oraclemaster:orcl]$ #vi scripts/login.sql SET SQLPROMPT ‘&_USER.@&_CONNECT_IDENTIFIER.> ‘ SET PAGESIZE 100 SET LINESIZE 16000 SET LONG 90000 Example: 

    # sudo su - oracle
[oracle@oraclemaster:orcl]$ sqlplus / as sysdba
SYS@orcl>
    ]]>

    Tagged ,
    2013-08-22
    Leave a comment
    MySQL, Oracle, Technology

    Oracle vs MySQL

  • MySQL:
    1. is very light, lightning fast,
    2. very easy to use,
    3. cost effective for start-ups,
    4. agile enough to cover any basic secure medium enterprise OLTP,
    5. I’m used to manage 1-10T table before having some issue,
    6. basically it’s divided into database and table (In the old MySQL 5.0, you can easily setup multiple  instances in one daemon. In the new MySQL 5.5, it’s recommended to run multiple instances in multiple daemons),
    7. replication is very easy to setup and easy to fix conflicts, however up until 5.6, there’s still many bugs,
    8. hot backup/snapshot is not coming by nature, but you can easily do hot backup/snapshot from stopped replicated server,
    9. MySQL Enterprise Admin is relatively useless for experienced DBA,
    10. can only replicate from one source,
    11. doesn’t have propagation checking like Oracle DataGuard,
    12. easier to have conflict in multiple active master,
    13. need to customize procedure to run similar Oracle materialized view,
    14. need to customize trigger to run similar Oracle audit,
    15. MySQL 5.6 starts to support account lock and password testing but through plugin method,
    16. MySQL Cluster is having all data in memory, risk of loosing data during power/server outage is high.
    • Oracle:
    1. heavyweight, resource consumed,
    2. better OLTP in term of complete microseconds DR,
    3. suitable for VLDB,
    4. more features,
    5. divided into database, schema (it’s more likely called database in MySQL) and table,
    6. online hot backup is easy to do on archivelog mode,
    7. Oracle Enterprise Manager supports more difficult tasks,
    8. have proprietary ASM storage,
    9. supports multiple active masters,
    10. supports slave check before commit (DataGuard),
    11. supports replication from multiple masters,
    12. supports materialized view,
    13. supports audit,
    14. supports more account features like account lock, password expiry, etc,
    15. supports function-based index and indexed-virtual column,
    16. supports role for privilege administration (similar to group privilege),
    17. Oracle cluster RAC is a real cluster on disc.
    Similarity:
    • From OLAP perspective, in my opinion both are offering similar features. OLAP depends more on DBA ability, rather than DB Servers ability.
    • Both supports encrypted replication and communication.
    How to run in MySQL vs in Oracle:
    1. displaying x numbers of row, e.g.: MySQL# select … limit x,x; Oracle# select … where rownum<=x;
    2. displaying a column in rows: MySQL has ‘\G’. Oracle has to use procedure like this: https://forums.oracle.com/thread/925884.
    3. MySQL has binary and varbinary as an addition to char and varchar. Oracle has nchar and nchar2 as an addition to char and varchar2.
    4. @>show tables in Oracle:@> select * from user_objects where object_type = ‘TABLE’;
    5. @>show create table in Oracle: @>select dbms_metadata.get_ddl(‘TABLE’, ‘TABLENAME’,’SCHEMANAME’) from dual;
    6. @>show index in Oracle: @>select dbms_metadata.get_ddl(‘INDEX’, ‘TABLENAME’,’SCHEMANAME’) from dual;
    Note:
    • OLTP = On-line Transactional Processing
    • OLAP = On-line Analytical Processing
    • VLDB = Very Large Data Bases.
    ]]>

    2013-08-21
    Leave a comment
    Oracle, Technology

    Learn Oracle in 1 hour: use Enterprise Manager and SQL Developer

  • Oracle Enterprise Manager (EM) is the web-based interface to manage Oracle server.
  • Oracle SQL Developer is a free Oracle database browser.
    • Use EM to understand how Oracle server works. Use SQL Developer to understand how to manage Oracle systems setting. Oracle Enterprise Manager: 
    https://[hostname]:1158/em/console/aboutApplication
    To start the dbconsole process: Navigate into your $ORACLE_HOME/bin directory Run the following statement: 
    ./emctl start dbconsole
    Additionally, you can stop the process and view its status. To stop the dbconsole process:sqlplus 
    ./emctl stop dbconsole
    To view the status of the dbconsole process: 
    ./emctl status dbconsole
    Oracle SQL Developer: To use SQL Developer simply install JavaSDK, download SQL Developer and run it using the java.exe. Create another superuser, therefore you can browse SYS without having to login to the server. Example: 
    CREATE USER supersu IDENTIFIED BY "0racle"
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE temp
quota unlimited on "USERS";
GRANT CONNECT, RESOURCE TO supersu;
GRANT DBA TO supersu;
    You need to understand Oracle structures and many important default views inside SYS (browse to Other Users), such as: SYS.dba_users, SYS.dba_profiles, SYS. user_profiles, etc. Done!]]>

    Tagged ,
    2013-08-14
    Leave a comment
    MySQL, Technology

    MySQL 5.6 chained replication stored procedure bug

    mysql1 -> mysql2 -> mysql3 -> mysql4 How to replicate the issue: (1) create a stored procedure, that accepts parameter, 

    create procedure tellme( IN name varchar(128), address varchar(128))
BEGIN
SET @localname=name, @localaddress=address;
...
END
    (2) call the procedure on mysql1 many times and sometimes you get the jackpot, that @localname and @localaddress are getting NULL value. Let’s look at the binlog: (1) When replication works well, you should see something like this: 
    # at 1058556811
#130509 14:13:03 server id 111  end_log_pos 1058556904  User_var
SET @`localname`:=_utf8 0x4334..D31394434 COLLATE `utf8_unicode_ci`/*!*/;

# at 1058556904
#130509 14:13:03 server id 111  end_log_pos 1058556973  User_var
SET @`localaddress`:=_utf8 0x726...656E69 COLLATE `utf8_general_ci`/*!*/;
    (2) When replication is broken, you see those SET missing. If the columns only accept not null value, then replication will stop. Avoid using local variable until next fix, use parameter directly.]]>

    Tagged , , , ,
    2013-08-14
    Leave a comment
    Oracle, Technology

    Oracle for beginner: a simple backup and recovery

    http://selectsysdatefromdual.gnusystems.net/?p=71 Add another parameter like existing -f, -a, etc and create your own level 0 and 1 (I prefer using level 0 and 1 rather than full backup), then you could easily set into cronjob. We do a simple testing, do a full backup of Oracle running in archivelog mode: 

    #rmanbackup.sh -f
    You should see backup(s) of data files, control files and archive logs: 
    RMAN> list backup;
    If you want to delete and manage the backups, simply use this reference: http://docs.oracle.com/cd/B19306_01/backup.102/b14192/maint003.htm Let’s start: 
    #echo $ORACLE_BASE
/home/oracle/app/oracle
SQL> shutdown abort;
$ cd /home/oracle/app/oracle/oradata/orcl
    Copy these all files and delete all of your data: 
    $ rm * -f
    Sure you can’t startup Oracle. You can fix it using 2 ways: (1) copy files back to the folder (2) using backups. Let’s use backups: 
    RMAN> startup nomount;
RMAN> restore controlfile from '/home/oracle/app/oracle/backups/controlspfile/c-1310390050-20110813-03';
    where c-1310390050-20110813-03 is your control file. If controlfile is corrupted, simply copy working control file to corrupted control file. Control file contains information about your data files, backup, etc. Once controlfile is recovered, do: 
    RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;
    We do resetlogs because it’s running in archivelog mode to reset remaining archive log. Done!]]>

    Tagged , , ,
    ← Older posts