vicker313 tech blog

October 28, 2009

Trace Oracle Database Transactions with LogMiner

Filed under: Oracle — Tags: , , — vicker313 @ 8:54 am

In Oracle file structure, there is one type of file called redo log file, which record down all the transactions done to the database. There will be a finite number of redo log files, which will keep on reusing the files in a Mary Go Round way. As the database is enabled with archived log, the redo log file will be duplicated into a copy of archived log before it is being reused.

Aside from recovering purpose, archived log can also be served as a transaction tracking material. However the log itself is in binary form, which we need a utility called LogMiner to decode the log file.

  1. Before you can use LogMiner you need to install the package by executing the following SQL file as SYS user:
    $ORACLE_HOME/rdbms/admin/dbmslm.sql
  2. Then grant necessary privilege to user who can execute LogMiner package (Let’s say user James):
    grant execute_catalog_role to james;
  3. Then create synonym for the LogMiner package:
    create public synonym dbms_logmnr for sys.dbms_logmnr;
  4. You can query the list of archived log:
    SELECT distinct member LOGFILENAME FROM V$LOGFILE;

There is 2 ways to read the archived log, one is to read the archived log at the database with the archived log, or move the archived log to another database and read it there. To read the log at the same database:

  1. Check where is your archived log placed (you may check it from the parameter file log_archive_dest). Assume that it is /u01/db/archive and the log you want to encode is 123.arc.
  2. Run statement below to add the archived log to the dictionary:
    exec DBMS_LOGMNR.ADD_LOGFILE('/u01/db/archive/123.arc');
  3. Then run statement below to start the LogMiner:
    exec DBMS_LOGMNR.START_LOGMNR(options => dbms_logmnr.dict_from_online_catalog);

To read the log at a different database:

  1. Move the archived logs to another database server (let’s say you placed in at /u01/temp)
  2. Run statement below to build a data dictionary for the archived log (you can change the file and directory):
    exec dbms_logmnr.build('logmnr.dat','/u01/temp');
  3. The run statement below to add the log file into the dictionary:
    exec DBMS_LOGMNR.ADD_LOGFILE('/u01/temp/123.arc');
  4. Start the LogMiner:
    exec DBMS_LOGMNR.START_LOGMNR(dictfilename => '/u01/temp/logmnr.dat');

Either way, you can query the log content through this view v$logmnr_contents. However the data in the view will be wiped off after you close the session, which mean you need to add the log file and start the LogMiner again in order to get back the data. Some useful columns include:

  1. username: database user used
  2. timestamp: when it is happened
  3. sql_redo: SQL statement of what actually been done
  4. sql_undo: SQL statement to undo the transaction
  5. scn: system change number, the unique id of this transaction
  6. seg_type_name: object type
  7. table_space: from which tablespace
  8. session#
  9. seg_owner: object owner
  10. table_name: table name
  11. operation: update, insert, delete, commit or rollback
  12. session_info: include info such as from which machine

October 21, 2009

Recover Strategy of Oracle Database with Archive Log Enabled

Filed under: Oracle — Tags: , , , , — vicker313 @ 8:00 am

After we setup the Backup Strategy, we need to prepare for recovering. Basically it involves 2 major steps: restore and recover.

Restore database simply means uncompress (such as UNTAR) the full backup from the backup tape:

  1. Uncompressed the full backup from tape (assume the tape is known as /dev/st0)
    tar xvf /dev/st0
  2. Don’t try to start the database first before you finish the next step, recovering database.

Different between restore and recover is that: restore is directly extracted from a backup, while recover is rebuilding data of an outdated database (such as from a full backup) to a status of particular moment.

  1. Remove all the archived log in the archived log directory (assume it is /u01/oracle/archive)
    rm -fv /u01/oracle/archive/*
  2. Uncompressed the daily backup (which is the archived log) .
    tar xvf /dev/st0
  3. Start SQLPLUS as SYSDBA.
    sqlplus / as sysdba
  4. Start database in mount mode
    startup mount;
  5. In case you have problem in startup the database because of the parameter file is not set, issue the command below and try again:
    create spfile from pfile = 'dest_of_your_parameter_file';
  6. Then issue command below to start recover from archived log:
    recover database using backup controlfile until cancel
  7. It will keep asking for your comfirmation on the name of the archived log to be recovered. Either press Enter, the location of the archived log, or key in cancel to stop the recover.
  8. After recover open the database by issuing command below, aware that you won’t able the continue recover the archived log after issue this command, or else you need to start all over again from restoring database:
    alter database open resetlogs;
  9. In case you face problem in open the database, add _allow_resetlogs_corruption=true to the parameter file, issue command below and try again.
    create sp file from pfile = 'dest_of_your_parameter_file';

October 14, 2009

Backup Strategy of Oracle Database with Archived Log Enabled

Filed under: Oracle — Tags: , , , , — vicker313 @ 11:03 am

Below is a backup strategy of Oracle Database with the conditions:

  1. The database is Archived Log Enabled.
  2. The database can be shutdown during weekend.

Archived log records down all the transactions that be performed in the database, which can serve as backup or trace log. It can even be backup when the database is up (hot backup). This strategy is using Linux environment, but you may apply the method to other OS. It involves 2 parts: daily backup (the hot backup) and weekend backup (or full backup, cold backup):

Daily Backup: AKA hot backup, which the backup is performed when database is up.

  1. This will be performed every night of weekday.
  2. Identify where is your archived log placed (refer to log_archive_dest parameter in the parameter file).
  3. Set CRON job to TAR all the archived log into Tape (assume /dev/st0 is your tape drive and /u01/oracle/archive is your archive log directory):
    tar cvf /dev/st0 /u01/oracle/archive
  4. You may check the status of the Tape using command below:
    mt status
    Try to install mt-st package in case the command is not installed.
  5. To verify the content of the tape and save it as a log by using command below:
    tar tvf /dev/st0 > dailybackup.log

Weekend Backup: AKA cold backup because it is performed when the database is shutdown.

  1. This will be performed during weekend.
  2. Shutdown the database (command might be different depend on your Oracle Environment Setting).
    service dbora stop
  3. TAR the whole database directory (assume /u01/oracle is where your database placed).
    tar cvf /dev/st0 /u01/oracle
  4. Verify the tape and save it as log.
    tar tvf /dev/st0 > fullbackup.log
  5. Remove the archived log (or the archived log will getting more and more until take out all your spaces).
    rm -fv /u01/oracle/archive/*
  6. Start the database.
    service dbora start

Continue to Recover Strategy.

October 7, 2009

Create Search Engine Friendly URL at Apache

Filed under: Apache — Tags: , — vicker313 @ 8:35 am

URL Query String is the string append to the web page URL. For example http://www.google.com/search?q=url+query+string, the string after the question mark (?) is the query string.

Most of the surfers will not care about the URL, which they consider them as alien language. However it would be nice if the URL can be translated into something more friendly to surfers, and most important to the search engine.

If your web server is Apache, you might follow the steps below to rewrite the URL (assume the folder of your website is webdir inside your document root):

  1. Enable the rewrite module in Apache. To do this, you need to edit the configuration file conf\httpd.conf.
  2. Find the following line and uncomment it.
    LoadModule rewrite_module modules/mod_rewrite.so
  3. In some scenario, you need to set the AllowOverride parameter to All as well.
    <Directory "document roots">
    ...
    AllowOverride All
    ...
    </Directory>
  4. Then restart the server.
  5. Create a file called .htaccess at webdir.
  6. Add the following coding or sample to .htaccess.
    RewriteEngine On
    RewriteBase /webdir/

    RewriteRule index.html$ index.php [NC]
    RewriteRule index/([^/]+)$ index.php?a=$1 [NC]
    RewriteRule index/([^/]+)/([^/]+)$ index.php?a=$1&b=$2 [NC]

Now when you type:

  • index.html, it will translate to index.php
  • index/category, it will translate to index.php?a=category
  • index/cateogory/linux, it will translate to index.php?a=category&b=linux

October 1, 2009

Setup VNC Server at Linux

Filed under: Linux — Tags: , — vicker313 @ 8:00 am

Steps to setup VNC Server at Linux:

  1. Install VNC Server (the name of the program might be different):
    1. Fedora (RPM): yum install vnc-server
    2. Ubuntu (DEB): sudo apt-get install vnc4server
  2. After install, start VNC Server(it will ask you to enter a password):
    vncserver :1
  3. Then stop VNC Server:
    vncserver -kill :1
  4. Edit ~/.vnc/xstartup (it will not appear if you didn’t start VNC Server once). Uncomment the following 2 lines  to enable the Windows X in VNC Server (the lines might be different):
    # unset SESSION_MANAGER
    # exec /etc/X11/xinit/xinitrc
  5. Start again VNC Server:
    vncserver :1
  6. In another machine, use your favorite VNC Client Program to access VNC Server:
    SERVER_IP:DISPLAY
    Display is the number after the colon you set at VNC Server. For example:
    192.168.1.33:1

Blog at WordPress.com.