me and my stuff

This will be update version of this post if you are using oracle 10g or 11g. Last month I post about export/import data in oracle using exp,imp utility. Actually oracle has something newer and better performance if you're using 10g or 11g. Assume that we want to export one table from one oracle db and import it into another oracle db. So create the folder /home/oracle/dbbackup on two oracle db server to store file[that will be created when exporting and importing]. Next, we need to tell oracle to know this folder, create directory on both db by run command below in sqlplus SQL> create directory dmpdir as '/home/oracle/dbbackup'; Directory created. SQL> Now on source database export table with command below. [oracle@k8n ~]$ expdp username/password DIRECTORY= dmpdir DUMPFILE= filename.dmp TABLES= tablename Export: Release 10.1.0.3.0 - Production on Friday, 09 May, 2008 10:54 ... [oracle@k8n ~]$ after finish you will have filename.dmp in /home/oracle/dbbackup, ftp ...

There are two ways to create a new database on existing oracle db server. One is using 'CREATE DATABASE' sql statement[this is too long,hard for me]. Another is using DBCA(Database Confguration Assistance), GUI Base Program. This way is more easy and I choose this way:). One note here is DBCA need X to run and I want to remote login to db server and run dbca from ssh, so ssh need '-X' for X11 forward[assume that DISPLAY variable on server is set, if not try 'export DISPLAY=localhost:0.0' on the server]. [poj@fedev ~]$ ssh -X -l oracle 192.168.1.59 oracle@192.168.1.59's password: Last login: Wed Apr 16 15:11:14 2008 from 192.168.1.122 /usr/X11R6/bin/xauth: creating new authority file /home/oracle/.Xauthority [oracle@k8n ~]$ cd /u01/app/oracle/product/10.1.0/db_1/bin [oracle@k8n bin]$ dbca

One of database box hang today. It doesn't let me ssh to see what happen, so I just shut it down and try to reboot again. But during boot process, I got "Kernel panic: VFS: Unable to mount root fs on...". err.. look like the disk is gone. Anyway, after few time of cold boot it give me a chance to backup my data:). I don't need all data in database[too many outdate data], so I decide to move some importance table to another database. Oracle has exp and imp utility to do this job. To export table test123 to file test123.dmp type follwing command. [oracle@Oracle-1 oracle]$ exp username/password TABLES=test123 FILE=test123.dmp Export: Release 8.1.7.0.1 - Production on Mon Apr 7 13:22:01 2008 (c) Copyright 2000 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production With the Partitioning option JServer Release 8.1.7.0.1 - Production Export done in US7ASCII character set and US7ASCII NCHAR character set About to ex...

Using cron to run sql script is a good idea to apply with a periodic database job. When running any scripts, cron doesn't know any environment variable. So, in the script, we always -use full path with execute command. -define all environment variable. ex. to see data in customer table in oracle database and log in customer_rpt.log, I create sql script[testsc1.sql] like this [oracle@oracle1 ~]$ cat testsc1.sql spool /home/oracle/customer_rpt.log select * from customer; quit; [oracle@oracle1 ~]$ next create shell script file to run sql script file. [oracle@oracle1 ~]$ cat mydbshell.cron #!/bin/bash ORACLE_HOME=/u01/app/oracle/product/10.1.0/db_1; export ORACLE_HOME; ORACLE_SID= my_sid ; export ORACLE_SID; /u01/app/oracle/product/10.1.0/db_1/bin/sqlplus user/password @/home/oracle/testsc1.sql [oracle@oracle1 ~]$ note that oracle_home, oracle_sid variable must be defined and sqlplus command called with full path. Now, we define new cron job by 'crontab -e' in console and make...