Drop Data File from Oracle Database

You want to start an Oracle Database, but fail because of 1 particular data file is missing (for some reason). And you want to drop the data file in order to start the database:

1. Log in SQLPLUS as SYSDBA
sqlplus / as sysdba
2. Start database in mount mode
start mount
3. Then remove the missing data file with statement below
alter database datafile 'missing datafile' offline drop

Install Oracle in Text Mode For Linux

Basically Oracle Installer doesn’t support text mode installation, which the Oracle Universal Installer need to be run on GUI. So to install Oracle in machine with Linux without GUI:

1. Find another Linux machine with GUI (must be Linux, Windows will not work)
2. SSH to the machine (let’s say IP is 192.168.1.2) that you want to install Oracle, with the -X (X11 forward) option:
   ssh -X oracle@192.168.1.2
3. Run the installer, and the OUI will run on the machine that you’re using (the one with GUI)
   ./runInstaller

Trace Oracle Database Transactions with LogMiner

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

Recover Strategy of Oracle Database with Archive Log Enabled

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';

Backup Strategy of Oracle Database with Archived Log Enabled

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.

Create Search Engine Friendly URL at Apache

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

Setup VNC Server at Linux

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

Change IP in Command Prompt

In Windows, we can issue the following command to set static IP address in Command Prompt:

netsh interface ip set address "Local Area Connection" static 192.168.0.10 255.255.255.0 192.168.0.1 1

where the 3 IPs are Machine’s IP, Subnet Mask and Gateway

Or to set dynamic IP:

netsh interface ip set address "Local Area Connection" dhcp

Change File Attribute in Command Prompt

To change file attributes in Windows using Command Prompt:

attrib [+|-][ahsr] filename

Where + means enable the attribute, - means disable the attribute.

a: Archive
h: Hidden
s: System File
r: Read Only

Example:

attrib +sh filename means make the file a hidden system file

attrib -r filename means make the file not read only

Transfer MySQL Database Offline

Transfer MySQL database offline means, stop MySQL service, copy the database directory and paste it to another location directly. However it will be difference when transferring MyISAM database and InnoDB database:

• MyISAM: simply copy past the database directory (data\databasename) will do since all the data files are inside the directory
• InnoDB: need to copy the whole data directory (data), which all the databases will be copied as well. After you paste the directory to the destination, you need to remove the ib_logfile0 and ib_logfile1 before start the MySQL service