Oracle datapump status

Oracle logoDuring a import run of a +100GB Oracle database I was curious what the current progress was. I discovered you can press Ctrl+C to go to an interactive command mode during a import of export using datapump (impdp of expdp). In the interactive you can request the current status of the job(s). It is also possible to stop, start and resume jobs. For all options just type ‘HELP’. See the example below:

Import> status

Job: SYS_IMPORT_FULL_02
Operation: IMPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /usr/local/oracle/import/a01act-20140811-%u.dmp
Dump File: /usr/local/oracle/import/a01act-20140811-01.dmp
Dump File: /usr/local/oracle/import/a01act-20140811-02.dmp
<cut>
Dump File: /usr/local/oracle/import/a01act-20140811-50.dmp
Dump File: /usr/local/oracle/import/a01act-20140811-51.dmp

Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: ****
Object Name: PTL*****
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Completed Rows: 122,772
Completed Bytes: 102,619,789,192
Percent Done: 49
Worker Parallelism: 1

Import>

So I’m almost halfway now.

To return to the logging mode:

Import> CONTINUE_CLIENT

Oracle ASM lifesaver

I had a very nasty experience with Oracle ASM recently. A long story short, ASM crashed on both our RAC nodes when removing a corrupted archive file on file and incarnation number (removing it on it’s name didn’t work). Starting the instance failed. When removing the failing diskgroup ASM was happy again. Luckily the failing diskgroup only housed archivelog files, except for one database it also held it’s index datafile.  So that one wouldn’t come up. If you’re in such a situation, you can use the Oracle tools amdu in combination with kfed to extract the missing datafile or archivelogs (in our situation) from a dismounted diskgroup. If I knew that from the start it saved me alot of frustrating hours.

Oracle 11g ASM disk discovery

During the installation of Oracle 11gR2 on Red Hat Enterprise Linux 5 I stumbled upon a incorrect default setting for ASM disk discovery when using ASM lib. The discovery path is set to ‘/dev/raw/sd*’, which is incorrect because ASM lib creates the devices under ‘/dev/oracleasm/disks’.

Another thing to be aware of is the ownership of the disks. You need to run ‘/etc/init.d/oracleasm configure’ in order to configure the owner. If you just hit return the ownership of the disks is set to root. Because you will start the Oracle installation as user oracle the disks will not appear in the discovery step during the installation.

(more…)