Stop Datapump jobs

It really gets frustrating when you are not able to stop nor kill a datapump job by just killing the script. Anyway, here are the steps to stop/kill it cleanly:

1. First gather the list of datapump jobs:

CONNECT / as sysdba
SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a11
COL operation LIKE state
COL job_mode LIKE state

SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED
-------------------- -------------------- ------------------- ------------------ ------------------- ------------------
SCOTT IMPDP_TEST IMPORT SCHEMA EXECUTING 1

Now to kill the job, we need to note 2 parameters which we will use in next step to stop the jobs:
OWNER_NAME (Which is SCOTT)
JOB_NAME (Which is IMPDP_TEST)

2. With that information, we can now stop and kill the job:
CONNECT scott/tiger
SET serveroutput on
SET lines 100
DECLARE
h1 NUMBER;
BEGIN
h1 := DBMS_DATAPUMP.ATTACH('IMPDP_TEST','SCOTT');
DBMS_DATAPUMP.STOP_JOB (h1);
END;
/

3. Check that the job has stopped and removed:

CONNECT / as sysdba
SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a11
COL operation LIKE state
COL job_mode LIKE state

SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;

no rows selected

How to Drop database in Oracle 10g and above

In order to drop database in 10g and above versions, it can be done in simple 3 easy steps:

1. Shutdown the database

SQL> shutdown immediate;

2. Startup in restricted mode

SQL> startup restrict mount;

3. Drop database

SQL> drop database;

Note: Please note that the above command does not clear off the archive log files. You will have to delete them maually.

ORA-12547: TNS:lost contact

I was getting the following error while trying to connect to database locally as a different user:

oracledbtst: error while loading shared libraries: libskgxp11.so: cannot open shared object file: No such file or directory
ERROR:
ORA-12547: TNS:lost contact

I was told that the Oracle Software from which the database was running was copied from another server.

Hence, to resolve it, the following was done:

In order to fix it, this is what I did:
1. Stopped the database and the listener running from the home of the database.

2. Gave correct permission to $ORACLE_HOME/bin/oracle:

$ chmod 6751 $ORACLE_HOME/bin/oracle

3. Relinked the Oracle binary as follows since the database version was 11.1 on Linux

$ cd $ORACLE_HOME/bin
$ relink all

4. Started my database and the listener

5. Tested the connection and it worked without any errors.

ORA-27140: attach to post/wait facility failed

I was having this error when another user was trying to connect to the database on the same user. The database was actually running as user oraA and the user who was trying to connect to it was oraB.

After doing some analysis, it was found that user oraA did not had oinstall as it primary group while oraB was having it. And the permission on $ORACLE_HOME/bin/oracle was -rwxr-xr-x

To fix it this is what I did:
1. Shutdown the database and listener as user oraA
2. As root user, added oinstall as primary group for oraA user:

# usermod -g oinstall oraA

3. Connected as oraA user and changed the permission or oracle binary as follows:

$ chmod 6751 $ORACLE_HOME/bin/oracle

$ ls -l $ORACLE_HOME/bin/oracle

-rwsr-s--x 1 oraA oinstall 196020393 Nov 29  2009 oracle

4. Started the listener and the database

5. Tested the connectivity from user oraB and it all worked.

Migrating database from 32 bit to 64 bit

In order to migrate oracle database from 32 bit to 64 bit, we used the following RMAN restore method.

1. Ensure the new 64 bit OS has the 64 bit Oracle binary installed and configured.

2. Create the pfile of the database on the 64 bit. This can simply be copied from 32 bit. Make sure it is updated with correct files locations if it is different on the new server.

3. Take the full backup of the database via RMAN on the 32 bit by running the following command:

RMAN> run 
{ 
allocate channel c1 type disk; 
allocate channel c2 type disk; 
backup database plus archivelog; 
backup current controlfile; 
} 

4. Copy backup files to the 64 bit server.

5. Start the database on 64 bit in nomount state:
SQL> startup nomount;

6. Connect to RMAN and set the dbid:
$ rman target /

RMAN> set dbid=<dbid from 32 server>; 

7. Restore the controlfile and database:

RMAN> restore controlfile from '<location of the backup>'; 
RMAN> alter database mount; 
RMAN> restore database; 

 

8. Recover database to the point you want. If all the backups are available, then recover the database to the latest archivelog:

RMAN> run 
{ 
set until sequence xxxx; 
recover database; 
} 

RMAN> exit 

9. Once the recovery is done, perform the following tasks in order to start the database on 64 bit platform:

SQL> recover database until cancel using backup controlfile; 
cancel 
SQL> alter database open resetlogs migrate; 
SQL> @ ?/rdbms/admin/utlirp.sql 

SQL> shutdown immediate;
SQL> startup;
SQL> @ ?/rdbms/admin/utlrp.sql

SQL> shutdown immediate;
SQL> startup

10. The database is now ready to be used.

NOTE: If the database that is migrated has a standby database as well, then the standby database should be recreated since the primary database was started in resetlogs mode.