Oracle DBA Tips and Techniques

By Arjun Raja

Archive for the ‘Administration’ Category

DATAPUMP – KILL JOBS ONLY USING THE DATAPUMP TOOL.

Posted by Arjun Raja on April 24, 2012

Killing an expdp or impdp process from the oslevel will not stop the job – so expdp or impdp jobs must be killed from the database level.

Example ?

Start an export job…..

Check the dba_datapump_jobs view…

SQL> select job_name,owner_name,state from dba_datapump_jobs;

JOB_NAME                               OWNER_NAME                     STATE
—————————— —————————— ——————————
SYS_EXPORT_FULL_10             OPS$ORACLE                     EXECUTING
SYS_EXPORT_FULL_01             OPS$ORACLE                     NOT RUNNING
SYS_EXPORT_FULL_05             OPS$ORACLE                     NOT RUNNING
SYS_EXPORT_FULL_03             OPS$ORACLE                     NOT RUNNING

SQL> exit

Kill the job from the oslevel …

cmi1d:/u03/oradata/export/cmi1d> ps -ef |grep expdp

oracle 26214652 18350150   0 13:27:38  pts/2  0:00 expdp / DUMPFILE=expdp_full_cmi1d_170611_132738_%U.dmp LOGFILE=expdp_full_cmi1d_170611_132738.log PARFILE=/u01/local/dbhk/scripts/par/expdp_full.par
cmi1d:/u03/oradata/export/cmi1d> kill -9 26214652
cmi1d:/u03/oradata/export/cmi1d>

Now check database again – a few minutes after the oslevel kill.

SQL> select job_name,owner_name,state from dba_datapump_jobs;

JOB_NAME                                      OWNER_NAME                     STATE
—————————— —————————— ——————————
SYS_EXPORT_FULL_10             OPS$ORACLE                     EXECUTING

View still shows the job is executing.

If you look at the export dump area – it is clear the job is still executing in the background.

-rw-r—–    1 oracle   oinstall  877879296 Jun 17  13:43 expdp_full_cmi1d_170611_132738_02.dmp
-rw-r—–    1 oracle   oinstall 1382027264 Jun 17 13:43 expdp_full_cmi1d_170611_132738_01.dmp

Therefore it is not a good idea to kill a datapump job only from the oslevel – the job will not stop.

This is the way to do it.

First attach to the job via expdp….

The job name is SYS_EXPORT_FULL_10  started by OPS$ORACLE – in the view above.

To attach to a job, you must connect as the owner of the job….therefore I am using the ” / ” for connection as ops$oracle

expdp / attach=SYS_EXPORT_FULL_10

Export: Release 11.2.0.2.0 – Production on Fri Jun 17 13:45:10 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_EXPORT_FULL_10
Owner: OPS$ORACLE
Operation: EXPORT
Creator Privs: TRUE
GUID: A5E2A4EB79A500E4E0430A35C8EC00E4
Start Time: Friday, 17 June, 2011 13:27:42
Mode: FULL
Instance: cmi1d
Max Parallelism: 2
EXPORT Job Parameters:
Parameter Name      Parameter Value:
CLIENT_COMMAND        /******** DUMPFILE=expdp_full_cmi1d_170611_132738_%U.dmp LOGFILE=expdp_full_cmi1d_170611_132738.log PARFILE=/u01/local/dbhk/scripts/par/expdp_full.par
COMPRESSION           ALL
INCLUDE_METADATA      1
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 2
Job Error Count: 0
Dump File: /u02/oradata/export/expdp_full_cmi1d_170611_132738_%u.dmp
size: 8,589,934,592
Dump File: /u02/oradata/export/expdp_full_cmi1d_170611_132738_01.dmp
size: 8,589,934,592
bytes written: 4,096
Dump File: /u02/oradata/export/expdp_full_cmi1d_170611_132738_02.dmp
size: 8,589,934,592
bytes written: 15,687,680

Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: EBS_GL_ARCHIVE
Object Name: GL_ACCT_BALANCES
Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 894
Completed Rows: 81,746,354
Worker Parallelism: 1

Worker 2 Status:
Process Name: DW01
State: EXECUTING
Object Schema: EBS_GL_ARCHIVE
Object Name: GL_JOURNAL_LINES
Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 894
Completed Rows: 91,359,564
Worker Parallelism: 1

Export>

Enter KILL_JOB to  kill job..

Export> KILL_JOB
Are you sure you wish to stop this job ([yes]/no): yes
.
Check again –

select job_name,owner_name,state from dba_datapump_jobs;

JOB_NAME                       OWNER_NAME                     STATE
—————————— —————————— ——————————
SYS_EXPORT_FULL_01             OPS$ORACLE                     NOT RUNNING
SYS_EXPORT_FULL_05             OPS$ORACLE                     NOT RUNNING
SYS_EXPORT_FULL_03             OPS$ORACLE                     NOT RUNNING

Posted in Administration | Leave a Comment »

WARNING MESSAGE IN ORACLE CONFIG MANAGER STATUS.

Posted by Arjun Raja on January 18, 2011

When checking the status of the Oracle Congfig Manager , you may get the following warning message at the end – if so the sql collection packages need to be reloaded.

EX- While checking the status below rcatd database had a warning message attached to it …..

[PROD] rcatd:/u01/oracle/product/10.2.0.4/rmand/ccr/bin > ./emCCR status
Oracle Configuration Manager – Release: 10.3.3.1.1 – Production
Copyright (c) 2005, 2010, Oracle and/or its affiliates. All rights reserved.
——————————————————————
Start Date 14-Nov-2010 11:05:07
Last Collection Time 18-Jan-2011 11:03:00
Next Collection Time 19-Jan-2011 11:03:00
Collection Frequency Daily at 11:03
Collection Status idle
Log Directory /u01/oracle/product/10.2.0.4/rmand/ccr/hosts/xxxxxx/log
Registered At 29-Mar-2010 11:03:09
Automatic Update On
Collector Mode Connected
HTTP Proxy xxxxxx\svc-proxy-oracle-cm@bc-uat-proxy.xxxxxx.local:xxxxxx

WARN: Oracle Configuration Manager database objects are not in sync with
the installed configuration collection scripts.
Refer to the Installation and Configuration documentation on reloading the
SQL collection packages.
SID Script
—- —–
rcatd collectconfig

[PROD] rcatd:/u01/oracle/product/10.2.0.4/rmand/ccr/bin >

Run following script to reload sql collection packages in the database rcatd.

cd $ORACLE_HOME/ccr/admin/scripts >

[PROD] rcatd:/u01/oracle/product/10.2.0.4/rmand/ccr/admin/scripts > ./installCCRSQL.sh collectconfig -s rcatd -r sys
Provide password for the SYSDBA user “sys”: enter sys password.
Successfully installed collectconfig in the database with SID=rcatd.
[PROD] rcatd:/u01/oracle/product/10.2.0.4/rmand/ccr/admin/scripts >

cd $ORACLE_HOME/ccr/bin > ./emCCR status –

[PROD] rcatd:/u01/oracle/product/10.2.0.4/rmand/ccr/bin > ./emCCR status
Oracle Configuration Manager – Release: 10.3.3.1.1 – Production
Copyright (c) 2005, 2010, Oracle and/or its affiliates. All rights reserved.
——————————————————————
Start Date 14-Nov-2010 11:05:07
Last Collection Time 18-Jan-2011 11:03:00
Next Collection Time 19-Jan-2011 11:03:00
Collection Frequency Daily at 11:03
Collection Status idle
Log Directory /u01/oracle/product/10.2.0.4/rmand/ccr/hosts/xxxxxx/log
Registered At 29-Mar-2010 11:03:09
Automatic Update On
Collector Mode Connected
HTTP Proxy xxxxxx\svc-proxy-oracle-cm@bc-uat-proxy.xxxxxx.local:xxxxxx

Fixed !

Posted in Administration | Tagged: , , | Leave a Comment »

CREATING PUBLIC DATABASE LINK FROM 10g to 11g Database.

Posted by Arjun Raja on January 14, 2011

Point to note when creating a new public database link from a 10g to 11g database….the password of the user in the target database (11g) needs to be enclosed within double quotes.

SQL> create public database link 10g_11g_link connect to test identified by test using ‘test’;

Database link created.

SQL> select * from dual@10g_11g_link;

ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from 10G_11G_LINK

SQL> drop public database link 10g_11g_link;

Database link dropped.

SQL> create public database link 10g_11g_link connect to test identified by “test” using ‘test’;

Database link created.

SQL> select * from dual@10g_11g_link;

D

X

SQL>

Therefore since 11g databases have case sensitive passwords, the password in the 11g database must be enclosed within double quotes.- otherwise invalid username/password errors are thrown while using the link.

Posted in Administration | Tagged: , | 1 Comment »

EXP USING COMPRESSION

Posted by Arjun Raja on January 5, 2011

To include via normal export ( not datapump ) a named pipe and compress exports to save space and also backup the dump to tape.

First create parfile – u01/local/scripts/par/exp_full.par

parfile looks like this –

COMPRESS=N
DIRECT=Y
CONSISTENT=Y
STATISTICS=NONE
FULL=Y
FILE=pipe

Shell script export part –

# run exp

cd /u02/oradata/admin/db}/dpdump

rm /u02/oradata/admin/db/dpdump/*

/usr/sbin/mknod /u02/oradata/admin/db/dpdump/pipe p

/usr/bin/compress < /u02/oradata/admin/db/dpdump/pipe > /u02/oradata/admin/db/dpdump/db_full_exp_`date +%d%m%Y%H%M`.dmp.Z &

exp \’/ as sysdba\’ parfile=/u01/local/scripts/par/exp_full.par

dsmc backup *.Z

Posted in Administration | Tagged: , | Leave a Comment »

DATABASE RESOURCE MANAGER SETUP

Posted by Arjun Raja on August 29, 2010

ORACLE DATABASE RESOURCE MANAGER

Posted in Administration, Oracle 11g | Tagged: | Leave a Comment »

NFS MOUNTED DISKS – ORA27054

Posted by Arjun Raja on August 26, 2010

During RMAN clones , the foll error might appear despite all backup files having 777 permissions if you are using NFS mounted disks mounted on another box.

ORA-19505: failed to identify file
“/ora_export_2/backup//df_714731184_147403_1″
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

Workaround –

alter system set event=”10298 trace name context forever, level 32” scope=spfile;

Restart instance and try a restore.

Once restore is complete , remove the event set and restart cloned instance.

It this does not work try getting the UNIX admin to mount the disks with the following options –

rw,bg,intr,hard,timeo=600,wsize=32768,rsize=32768,nfserver=3,tcp

Posted in Administration | Tagged: | 1 Comment »

DELETE DUPLICATES IN TABLE

Posted by Arjun Raja on September 9, 2009

DELETE DUPLICATES IN A TABLE –

EX – TABLE TEST

SQL> desc test;

COL_1 NUMBER(10)
COL_2 DATE

SQL>

select count(*) from test WHERE ROWID IN
(select rowid from test
minus
select max(rowid) from test
group by
col_1,col_2);

delete from test WHERE ROWID IN
(select rowid from test
minus
select max(rowid) from test
group by
col_1,col_2);

x——–x———-x———x———-x

Posted in Administration | Tagged: | Leave a Comment »

ORA-19809: limit exceeded for recovery files

Posted by Arjun Raja on August 24, 2009

ALERT LOG ERROR  – ORA-19809

If the archivelog desintation as suggested by the parameter db_recovery_file_dest has used all the capacity as indicated in the db_recovery_file_dest_size parameter , then Oracle will not archive the next log until space is added or freed up.

The following error will appear in the alert log if you try to shutdown a database or switch a log file –

ORACLE Instance flash – Archival Error
ORA-16038: log 1 sequence# 43 cannot be archived
ORA-19809: limit exceeded for recovery files

To overcome this error after you have issued the Shutdown Immediate command –

1.  Shutdown abort;

2.  Startup mount;

3.

SQL>

set lines 100

col name format a60

select  name,  (space_limit/1048576),(space_used/1048576)from  v$recovery_file_dest;

NAME                                                         (SPACE_LIMIT/1048576)
———————————————————— ———————
(SPACE_USED/1048576)
——————–
/u02/oradata/flash_recovery_area                                              2048
            2184.979

 SQL> alter system set db_recovery_file_dest_size=4096m scope=both;

SQL> alter database open;

Database altered.

SQL>

Now shutdown immediate;

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Posted in Administration, High Availability | Tagged: , , | 1 Comment »

Flashback database – with and without resetlogs.

Posted by Arjun Raja on August 11, 2009

FLASHBACK DATABASE – IMPACT OF RESETLOGS-

A flashback database can be completed and the database can either be opened for READ ONLY or opened with a RESETLOGS option.

As long as the RESETLOGS option is not used, a flashback can be rolled back .

Example –

Create table test – insert some records –

select count (*) from test ;

COUNT(*)
———-
100000

Select current_scn from v$database;

653026

Switch a couple of logfiles –

alter system switch logfile;

Insert another 50000 records into table test.

select count (*) from test ;

COUNT(*)
———-
150000

select first_change# ,to_char(first_time,'DD-MON-YYYY HH24:MI:SS'),sequence# from V$LOG_HISTORY ORDER BY 1 ;

FIRST_CHANGE# TO_CHAR(FIRST_TIME,’ SEQUENCE#
————- ——————– ———-
538113 11-AUG-2009 10:58:17 1
576251 11-AUG-2009 11:03:35 2
653024 11-AUG-2009 11:18:11 3
653026 11-AUG-2009 11:18:12 4
653029 11-AUG-2009 11:18:17 5

Now shutdown immediate and flashback database to SCN 653026 – Before the latest insert to table test – at which point the count(*) of test is 100,000.

shutdown immediate;

startup mount ;

Now flashback database to SCN 653026.

SQL> flashback database to scn 653026;

Now open the database READ ONLY –

alter database read only;
select count(*) from test;

COUNT(*)
———-
100000

If you are satisfied with the results, the database can be opened with the RESETLOGS option –

Shutdown immediate;
startup mount;
alter database open resetlogs;

If not satisfied and you want to redo the flashback or get the database back to it’s current state-

Shutdown immediate;
startup mount;
recover database;

Media recovery complete.

alter database open;

Database altered.

select count(*) from test;

COUNT(*)
———-
150000

Therefore as long as a RESETLOGS is not mentioned, the flashback of a database can be reversed.

Ex – if you use RESETLOGS –

Shutdown immediate;

Startup mount;

flashback database to scn 653026;

Flashback complete.

alter database open resetlogs;

Database altered.

select count(*) from test;

COUNT(*)
———-
100000

shutdown immediate;

startup mount;

recover database;

SQL> ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

alter database open;

Database altered.

select count(*) from test;

COUNT(*)
———-
100000

Therefore the RESETLOGS option does not allow you to reverse the FLASHBACK of the database.

Posted in Administration | Tagged: , | Leave a Comment »

How to check the UTL_FILE_DIR parameter?

Posted by Arjun Raja on August 7, 2009

CREATE OR REPLACE procedure utl_file_test_write1 (
path in varchar2,
filename in varchar2,
firstline in varchar2,
secondline in varchar2)
is
output_file utl_file.file_type;
begin
output_file := utl_file.fopen (path,filename, ‘W’);

utl_file.put_line (output_file, firstline);
utl_file.put_line (output_file, secondline);
utl_file.fclose(output_file);

–exception
— when others then null;
end;
/

begin
utl_file_test_write1 (
‘/u03/users/ofsa/ofsatest/oracle_utl_data’,
‘utl_file_test’,
‘first line’,
‘second line’
);
end;
/

This works too –

SET SERVEROUTPUT ON
DECLARE
fid UTL_FILE.FILE_TYPE;
v VARCHAR2(32767);
PROCEDURE recNgo (str IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE (‘UTL_FILE error ‘ || str);

UTL_FILE.FCLOSE (fid);
END;
BEGIN
/* Change the directory name to one to which you at least
|| THINK you have read/write access.
*/
fid := UTL_FILE.FOPEN (‘/u03/users/ofsa/ofsatest/oracle_utl_data’, ‘utl_file_test’, ‘R’);
UTL_FILE.GET_LINE (fid, v);
dbms_output.put_line (v);
UTL_FILE.FCLOSE (fid);

fid := UTL_FILE.FOPEN (‘/u03/users/ofsa/ofsatest/oracle_utl_data’, ‘utl_file_test_10’, ‘W’);
UTL_FILE.PUT_LINE (fid, v);
UTL_FILE.FCLOSE (fid);
EXCEPTION
WHEN UTL_FILE.INVALID_PATH
THEN recNgo (‘invalid_path’);
WHEN UTL_FILE.INVALID_MODE
THEN recNgo (‘invalid_mode’);
WHEN UTL_FILE.INVALID_FILEHANDLE
THEN recNgo (‘invalid_filehandle’);
WHEN UTL_FILE.INVALID_OPERATION
THEN recNgo (‘invalid_operation’);
WHEN UTL_FILE.READ_ERROR
THEN recNgo (‘read_error’);
WHEN UTL_FILE.WRITE_ERROR
THEN recNgo (‘write_error’);
WHEN UTL_FILE.INTERNAL_ERROR
THEN recNgo (‘internal_error’);
END;
/

message for success below.

first line

PL/SQL procedure successfully completed.

SQL> exit

Posted in Administration | Tagged: , | Leave a Comment »