ODbaBlogs: July 2014

Tuesday, July 8, 2014

Work on jobs for expdp an impdp

Work on jobs for expdp an impdp

While using the expdp use job_name clause to create a user defined job.

[oracle@myserver01 ABC11TST]$ cat par_test.par

directory=DATA_PUMP_DIR

dumpfile=ABC11TST_testing.dmp

logfile=ABC11TST_testing.log

JOB_NAME=test123

full=y

run the expdp and starts play with jobs

[oracle@myserver01 ABC11TST]$ expdp parfile=par_test.par

Export: Release 11.1.0.7.0 - 64bit Production on Sunday, 06 July, 2014 22:34:30

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYS"."TEST123": /******** AS SYSDBA parfile=par_test.par

Estimate in progress using BLOCKS method...

Once expdp is running:

To Stop/Start/Kill the jobs check the job status and use below to stop/kill/start

SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE

---------- -------------- --------------- ---------- --------

SYS TEST123 EXPORT FULL EXECUTING

[oracle@myserver01 ~]$ ps -ef |grep exp

root 3789 1 0 Feb02 ? 00:00:24 gpm -m /dev/input/mice -t exps2

oracle 5477 8147 0 22:34 pts/2 00:00:00 expdp

oracle 5535 1552 0 22:34 pts/1 00:00:00 grep exp

its internal process is DW01 [n]

myserver01:GEM11TST\sys> !ps -ef |grep -i DW01

oracle 5492 1 84 22:34 ? 00:00:55 ora_dw01_ABC11TST

oracle 5672 5652 0 22:35 pts/1 00:00:00 /bin/bash -c ps -ef |grep -i DW01

To Stop the job:

Use attach clause to get into the job like below:

[oracle@myserver01~]$ expdp attach=sys.TEST123

Export: Release 11.1.0.7.0 - 64bit Production on Sunday, 06 July, 2014 22:43:06

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: TEST123

Owner: SYS

Operation: EXPORT

Creator Privs: TRUE

GUID: FD94D4B483EF4A7BE040140A2613156C

Start Time: Sunday, 06 July, 2014 22:38:04

Mode: FULL

Instance: ABC11TST

Max Parallelism: 1

EXPORT Job Parameters:

Parameter Name Parameter Value:

CLIENT_COMMAND /******** AS SYSDBA parfile=par_test.par

State: EXECUTING

Bytes Processed: 0

Current Parallelism: 1

Job Error Count: 0

Dump File: /orabkup_nonprod/ABC11TST/ABC11TST_testing.dmp

bytes written: 4,755,456

Worker 1 Status:

Process Name: DW01

State: EXECUTING

Object Schema: TEST_SOAINFRA

Object Name: REFERENCE_INSTANCE

Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Completed Objects: 1

Total Objects: 879

Completed Rows: 98,326

Worker Parallelism: 1

Export> STOP_JOB=IMMEDIATE

Are you sure you wish to stop this job ([yes]/no): yes

[oracle@myserver01 ~]$

Now again check the status

SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE

---------- -------------- -------------- ---------- ----------------

SYS TEST123 EXPORT FULL NOT RUNNING

[oracle@myserver01 ~]$ ps -ef |grep -i expdp

oracle 5840 5759 0 22:37 pts/1 00:00:00 grep -i expdp

[oracle@myserver01 ~]$

[oracle@myserver01 ~]$ ps -ef |grep -i 5492

oracle 5847 5759 0 22:37 pts/1 00:00:00 grep -i 5492

[oracle@myserver01 ~]$ ps -ef |grep -i 5477

oracle 5854 5759 0 22:37 pts/1 00:00:00 grep -i 5477

[oracle@myserver01 ~]$

Now start the Job:

[oracle@myserver01~]$ expdp attach=sys.TEST123

Export: Release 11.1.0.7.0 - 64bit Production on Sunday, 06 July, 2014 22:37:59

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: TEST123

Owner: SYS

Operation: EXPORT

Creator Privs: TRUE

GUID: FD94D4B483EF4A7BE040140A2613156C

Start Time: Sunday, 06 July, 2014 22:38:04

Mode: FULL

Instance: ABC11TST

Max Parallelism: 1

EXPORT Job Parameters:

Parameter Name Parameter Value:

CLIENT_COMMAND /******** AS SYSDBA parfile=par_test.par

State: IDLING

Bytes Processed: 0

Current Parallelism: 1

Job Error Count: 0

Dump File: /orabkup_nonprod/ABC11TST/ABC11TST_testing.dmp

bytes written: 4,751,360

Worker 1 Status:

Process Name: DW01

State: UNDEFINED

Object Schema: TEST_SOAINFRA

Object Name: REFERENCE_INSTANCE

Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Completed Objects: 1

Total Objects: 879

Completed Rows: 9,076

Worker Parallelism: 1

Export> START_JOB

Export> status

Job: TEST123

Operation: EXPORT

Mode: FULL

State: EXECUTING

Bytes Processed: 0

Current Parallelism: 1

Job Error Count: 0

Dump File: /orabkup_nonprod/ABC11TST/ABC11TST_testing.dmp

bytes written: 4,755,456

Worker 1 Status:

Process Name: DW01

State: EXECUTING

Object Schema: TEST_SOAINFRA

Object Name: REFERENCE_INSTANCE

Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Completed Objects: 1

Total Objects: 879

Completed Rows: 88,962

Worker Parallelism: 1

Export> exit

[oracle@myserver01~]$ ps -ef |grep -i DW01

oracle 5952 1 9 22:38 ? 00:00:16 ora_dw01_ABC11TST

oracle 6257 5877 0 22:41 pts/1 00:00:00 grep -i dw01

[oracle@myserver01~]$ ps -ef |grep -i expdp

oracle 6299 5877 0 22:41 pts/1 00:00:00 grep -i expdp

[oracle@myserver01~]$

SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE

---------- -------------- --------------- ---------- --------

SYS TEST123 EXPORT FULL EXECUTING

To Kill the Jobs:

Check job status

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE

--------------- --------------- -------------------- ------------------- ---------------

SYS TEST123 EXPORT FULL EXECUTING

[oracle@myserver01 ~]$ ps -ef |grep expdp

oracle 12703 8147 0 00:05 pts/2 00:00:00 expdp

oracle 15100 15053 0 00:36 pts/1 00:00:00 grep expdp

[oracle@myserver01 ~]$ ps -ef |grep DW01

oracle 15102 15053 0 00:36 pts/1 00:00:00 grep DW01

[oracle@myserver01 ~]$

Attach to job

[oracle@myserver01 ~]$ expdp ATTACH=sys.TEST123

Export: Release 11.1.0.7.0 - 64bit Production on Monday, 07 July, 2014 0:36:14

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: TEST123

Owner: SYS

Operation: EXPORT

Creator Privs: TRUE

GUID: FD96194AF4EA35C6E040140A261331A8

Start Time: Monday, 07 July, 2014 0:05:25

Mode: FULL

Instance: ABC11TST

Max Parallelism: 1

EXPORT Job Parameters:

Parameter Name Parameter Value:

CLIENT_COMMAND /******** AS SYSDBA parfile=par_test.par

State: EXECUTING

Bytes Processed: 0

Current Parallelism: 1

Job Error Count: 0

Dump File: /orabkup_nonprod/ABC11TST/ABC11TST_testing.dmp

bytes written: 4,751,360

Worker 1 Status:

Process Name: DW01

State: EXECUTING

Object Schema: TEST_SOAINFRA

Object Name: REFERENCE_INSTANCE

Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Completed Objects: 1

Total Objects: 879

Completed Rows: 241,615

Worker Parallelism: 1

Export> exit

Export> KILL_JOB

Are you sure you wish to stop this job ([yes]/no): yes

[oracle@myserver01 ~]$

After killed the job everything will gone.

[oracle@myserver01 ~]$ expdp attach=sys.TEST123

Export: Release 11.1.0.7.0 - 64bit Production on Monday, 07 July, 2014 0:46:24

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-31626: job does not exist

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: at "SYS.KUPV$FT", line 386

ORA-31638: cannot attach to job TEST123 for user SYS

ORA-31632: master table "SYS.TEST123" not found, invalid, or inaccessible

ORA-00942: table or view does not exist

[oracle@myserver01 ~]$ ps -ef |grep expdp

oracle 15543 15053 0 00:41 pts/1 00:00:00 grep expdp

[oracle@myserver01 ~]$ ps -ef |grep DW01

oracle 15548 15053 0 00:41 pts/1 00:00:00 grep DW01

[oracle@myserver01 ~]$

SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

no rows selected

Issue Faced during Job stop and start:

ORA-39095

Job "SYS"."TEST123" stopped due to fatal error at 22:09:39

Job TEST123 has been reopened at Sunday, 06 July, 2014 22:09

Restarting "SYS"."TEST123": /******** AS SYSDBA parfile=par_test.par

ORA-39095: Dump file space has been exhausted: Unable to allocate 0 bytes

Job "SYS"."TEST123" stopped due to fatal error at 22:10:19

These kind of error comes for couple of reason.

1) If you use parallel option while exporting through expdp

2) Another reason if you are using compression method in expdp.

Both reason will cause the same error.

Error comes while starting the job, job will stop and it won’t be able to start. So avoid using compression and parallel in such scenario.

EXPDP and IMPDP through Network link

EXPDP and IMPDP through network link:

For EXPDP

1) Create database link on target database.

myserver01:ABC11TST\sys> create public database link eqedge_to_gem connect to scott identified by amaresh123 using 'EQEGDEV';

Database link created.

myserver01:ABC11TST\sys> select * from dual@eqedge_to_gem;

1) grant EXP_FULL_DATABASE privs to the source user or db link created user (source database)

[oracle@myserver01 ~]$ export ORACLE_SID=ABCDEV

[oracle@myserver01 ~]$ sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jul 7 22:18:47 2014

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant EXP_FULL_DATABASE to scott;

Grant succeeded.

If we will not give this privs below error will come.

ORA-31631: privileges are required

ORA-39149: cannot link privileged user to non-privileged user

Now create a parfile on target backup location

myserver01:ABC11TST\sys> create or replace directory DATA_PUMP_DIR as '/orabkup_nonprod/ABC11TST';

Directory created.

[oracle@myserver01 ABC11TST]$ pwd

/orabkup_nonprod/ABC11TST

[oracle@myserver01 ABC11TST]$ cat par_test_network.par

directory=DATA_PUMP_DIR

dumpfile=IQSONAR_testing.dmp

logfile=IQSONAR_testing.log

JOB_NAME=test123

schemas=IQSONAR

network_link=eqedge_to_gem

now use the expdp to take export backup.

[oracle@myserver01 ABC11TST]$ expdp parfile=par_test_network.par

Export: Release 11.1.0.7.0 - 64bit Production on Monday, 07 July, 2014 22:42:10

Username: aniraj/amaresh23

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "ANIRAJ"."TEST123": aniraj/******** parfile=par_test_network.par

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 0 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Master table "ANIRAJ"."TEST123" successfully loaded/unloaded

******************************************************************************

Dump file set for ANIRAJ.TEST123 is:

/orabkup_nonprod/ABC11TST/IQSONAR_testing.dmp

Job "ANIRAJ"."TEST123" successfully completed at 22:42:24

Check the dump file at target backup directory location:

[oracle@myserver01 ABC11TST]$ pwd

/orabkup_nonprod/ABC11TST

[oracle@fcdbfrm14 ABC11TST]$ ls -ltr IQSONAR_testing.dmp IQSONAR_testing.log

-rw-r----- 1 oracle oradba 147456 Jul 7 22:41 IQSONAR_testing.dmp

-rw-r--r-- 1 oracle oradba 1021 Jul 7 22:41 IQSONAR_testing.log

[oracle@fcdbfrm14 ABC11TST]$

For IMPDP

Grant below privs to schema through which you are going to take expdp:

NOTE: on source database user which is being user in dblink must have EXP_FULL_DATABASE privs as well if we are going to expdp for full db or any other schemas of source db.

myserver01:ABC11TST\sys> grant IMP_FULL_DATABASE to aniraj;

Grant succeeded.

Create parfile for impdp

No dumpfile parameter needed here to import through network link

[oracle@myserver01 ABC11TST]$ cat impdp_test_network.par

directory=DATA_PUMP_DIR

logfile=impdp_scott_eqd.log

JOB_NAME=test123

schemas=SCOTT

remap_schema=SCOTT:AMARESH

network_link=eqedge_to_gem

[oracle@myserver01 ABC11TST]$ impdp parfile=impdp_test_network.par

Import: Release 11.1.0.7.0 - 64bit Production on Monday, 07 July, 2014 22:31:04

Username: aniraj/amaresh123

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "ANIRAJ"."TEST123": aniraj/******** parfile=impdp_test_network.par

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 192 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

. . imported "AMARESH"."DEPT" 4 rows

. . imported "AMARESH"."EMP" 14 rows

. . imported "AMARESH"."SALGRADE" 5 rows

. . imported "AMARESH"."BONUS" 0 rows

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "ANIRAJ"."TEST123" successfully completed at 22:31:15

select name from v$database;

NAME

---------

ABC11TST

select count(*) from dba_tables where owner='AMARESH';

COUNT(*)

--------------------

Friday, July 4, 2014

Database Health checkup script

Database Health checkup script

This script need to run with INSTANCE_NAME. without this it will errored out where script is connecting to db.
Script Name : db_healthcheck.sh

run like ./db_healthcheck.sh <Instance Name>

Script is below

#/bin/ksh

#Create by Amaresh

#Created Date 03/12/2009

#Modified Date 03/07/2014

# This script is created for doing quick health check.

export logfile=/tmp/healthcheck.log

export ORACLE_SID=$1

#System uptime

echo -e "\n\n============================System Uptime================================"

echo hostname

hostname

echo

uptime

echo "==============================End System Uptime=============================="

echo -e "\n\n===========================Sar Output===================================="

sar 2 10

echo "==============================End Sar Output================================="

#echo -e "\n\n==========================Memory Free Stats=============================="

#free -m

#echo "=============================End Memory Status==============================="

echo -e "\n\n============================Disk Status=================================="

df -h

echo "==============================End Disk Status================================"

echo -e "\n\n================Top 20 process Stats 3 times from Top command============"

echo First Time

echo

top -c -n 1|head -30

echo

echo 2nd Time

top -c -n 1|head -30

echo

echo 3rd Time

top -c -n 1|head -30

echo

echo " =================================End of Top Command==========================="

echo -e "\n\n=======================Listener Status==================================="

echo

#ps -ef |grep tns

ps -ef |grep tns|grep inherit

lsn=`ps -ef |grep tns|grep -v grep| awk '{print $9}'`

echo $lsn

lsnrctl status $lsn

echo

echo "=========================End Listener Status================================="

echo -e "\n\n================Oracle Enterprise Manager================================"

ps -ef |grep -v grep|grep -i "/bin/emagent"

agent=`ps -ef |grep -i "/bin/emagent"|grep -v grep| awk '{print $8}'|rev| cut -c 8- | rev`

echo $agent

cd $agent

./emctl status agent

echo "=========================End Enterprise Manager=============================="

echo -e "\n\n========================================================================="

echo -e "\n\n=======================Checkign Backup logs====================================="

echo -e "\n\n========Archive Log Backup checks ==========="

ls -ltr /orabkup/$ORACLE_SID/rman_arch*.log

echo -e "\n\n========RMAN Full Backup checks ============="

ls -ltr /orabkup/$ORACLE_SID/rman_$ORACLE_SID*.log

echo -e "\n\n========Export Backup checks ================"

ls -ltr /orabkup/$ORACLE_SID/exp*.log

echo -e "\n\n========Backup checksthrough RMAN ==========="

sqlplus /nolog <<!

conn /as sysdba

set lines 120

set pages 1000

col RMAN_Status FORMAT A20 heading "Status"

col INPUT_TYPE FORMAT A15 heading "Backup Type"

col Hrs FORMAT 999.99 heading "Backup Time"

col Start_Time FORMAT A20 heading "Backup Start Time"

col End_Time FORMAT A20 heading "Backup End Time"

SELECT INPUT_TYPE,

TO_CHAR(START_TIME,'DY mm/dd hh24:mi') Start_Time,

TO_CHAR(END_TIME,'DY mm/dd hh24:mi') End_Time,

ELAPSED_SECONDS/3600 Hrs,

STATUS RMAN_Status

FROM V\$RMAN_BACKUP_JOB_DETAILS where START_TIME > sysdate -15

ORDER BY SESSION_KEY desc;

echo "=========================End Backup Checks==================================="

echo -e "\n\n========================================================================="

echo -e "\n============================Database statistics============================"

echo -e "\n==========================================================================="

# check startup time

export blk_size=`

sqlplus -s /nolog <<END

conn /as sysdba

clear columns

set pagesize 0 feedback off verify off heading off echo off

show parameter db_block_size

exit;

END`

block_size=`echo $blk_size| awk '{print $3}'`

#echo $block_size

echo -e "\n=============Checks for AUTO EXTEND Data file and TS Utilization======================"

sqlplus /nolog <<!

conn /as sysdba

select instance_name, inst_id,to_char(startup_time,'dd/mm/yyyy hh24:mi:ss') startuptime from gv\$instance;

set line 150

set pages 1000

define block_size =$block_size;

col TABLESPACE_NAME for a30

select a.tablespace_name, b.no_of_data_files , b.max_size_M,a.BLOCK_SIZE,a.ALLOCATION_TYPE,a.SEGMENT_SPACE_MANAGEMENT,a.EXTENT_MANAGEMENT,b.AUTOEXTENSIBLE from dba_tablespaces a , (select tablespace_name, AUTOEXTENSIBLE, count(*) as no_of_data_files , sum(maxblocks*&block_size)/1024/1024 as max_size_M from dba_data_files group by tablespace_name,AUTOEXTENSIBLE) b where a.tablespace_name=b.tablespace_name;

set line 199

set pages 1000

col TABLESPACE_NAME for a30

col file_name for a50

show parameter db_block_size

select tablespace_name, file_name, AUTOEXTENSIBLE, sum(maxblocks*&block_size)/1024/1024 as max_size_M from dba_data_files group by tablespace_name, file_name, AUTOEXTENSIBLE order by 1;

!echo checking file ststus

select distinct status from dba_data_files;

select count(*) from v\$recover_file;

!echo Tablespace Utiliazation Report

column tablespace_name heading "TableSpace" format a30

column total_space heading "TotalSpace" format 9999999

column free_space heading "FreeSpace" format 9999999

column used heading "UsedSpace" format 9999999

column pct heading "Used %" format 999.99

select a.tablespace_name,total_space,free_space,

(total_space-free_space) used,

((total_space-free_space)/total_space)*100 pct

from

(select tablespace_name,sum(bytes)/(1024*1024) total_space

from dba_data_files

group by tablespace_name) a,

(select tablespace_name,sum(Bytes)/(1024*1024) free_space

from dba_free_space

group by tablespace_name) b

where a.tablespace_name = b.tablespace_name(+)

order by 5 desc;

!echo checking blocking locks

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,

id1, id2, lmode, request, type

FROM V\$LOCK

WHERE (id1, id2, type) IN

(SELECT id1, id2, type FROM V\$LOCK WHERE request>0)

ORDER BY id1, request;

!echo Session holding Library cache pin locks

select /*+ ordered */ w1.sid waiting_session,

h1.sid holding_session,

w.kgllktype lock_or_pin,

w.kgllkhdl address,

decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_held,

decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_requested

from dba_kgllock w, dba_kgllock h, v\$session w1, v\$session h1

where (((h.kgllkmod != 0) and (h.kgllkmod != 1)

and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))

and (((w.kgllkmod = 0) or (w.kgllkmod= 1))

and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))

and w.kgllktype = h.kgllktype

and w.kgllkhdl = h.kgllkhdl

and w.kgllkuse = w1.saddr

and h.kgllkuse = h1.saddr;

echo -e "\n\n==================================END of Database status report========="

echo -e "\n\n========================== DR checks ==================================="

sqlplus /nolog <<!

conn /as sysdba

clear column

!echo "================ Checks for DR parameters ================"

!echo "================== Checks for DR parameters ================"

show parameter log_archive_dest_state_2

show parameter log_archive_dest_2

set line 150

set pages 1000

select switchover_status from v\$database;

!echo "=============================== Checks DR Log Applied and Log Archived =============="

!echo -e "\n \n"

select rtrim(ltrim(max(al.sequence#))) as LOG_APPLIED from v\$archived_log al, v\$log_history lh where al.sequence# = lh.sequence# and al.applied='YES' and lh.sequence# is NOT NULL and al.thread#=1;

select rtrim(ltrim(max(al.sequence#))) as LOG_ARCHIVED from v\$archived_log al, v\$log_history lh where al.sequence# = lh.sequence# and al.ARCHIVED='YES' and lh.sequence# is NOT NULL and al.thread#=1;

select PROCESS,STATUS,CLIENT_PROCESS,SEQUENCE# from V\$MANAGED_STANDBY;

echo -e "\n\n===================Last 50 Lines of Alertlog for DR checks=============="

VALUE=`

sqlplus -s /nolog <<END

conn /as sysdba

clear columns

set pagesize 0 feedback off verify off heading off echo off

show parameter background_dump_dest;

exit;

END`

echo $VALUE

tmp=`echo $VALUE| awk '{print $3}'`

echo $tmp

tail -50 $tmp/alert_*.log

echo -e "\n\n\n\n\n======================End of Database alter Messaes================"

Please give me feedback if you want to anything in this script, Will try to put those as well.

Tuesday, July 1, 2014

Configure Direct NFS Client (DNFS) on Linux

Step by Step - Configure Direct NFS Client (DNFS) on Linux for Oracle11g:

1 Shutdown the database
sqlplus "/as sysdba"
shutdown immediate
exit

2 Enable the Direct NFS Client
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dnfs_on

Verify:

ls -l $ORACLE_HOME/lib/lib*od*11.so

-rw-r--r--. 1 oracle oinstall 60431 Sep 17 2011 $OH/lib/libnfsodm11.so << dnfs

-rw-r--r--. 1 oracle oinstall 60431 Oct 20 19:45 $OH/lib/libodm11.so # Oracle used

-rw-r--r--. 1 oracle oinstall 12259 Sep 17 2011 $OH/lib/libodmd11.so << no dnfs

( Disable the Direct NFS Client

cd $ORACLE_HOME/rdbms/lib

make –f ins_rdbms.mk dnfs_off )

Oracle processes use /etc/mtab to access NFS settings

3 Configure ORANFSTAB

Direct NFS Client can use a new configuration file or the mount tab file (/etc/mtab on Linux) to determine the mount point settings for NFS storage devices.

This file is required only for configuring the Direct NFS for load balancing and specfic to single database.You can still enable the Direct NFS without configuring oranfstab file.DNFS will take mount point settings for NFS from /etc/mtab on Linux

In RAC,the oranfstab must be configured on all nodes and keep /etc/oranfstab file synchronized on all nodes.

(When the oranfstab file is placed in $ORACLE_HOME/dbs, the entries in the file are specific to a single database. In this case, all nodes running an Oracle RAC database use the same ORACLE_HOME/dbs/oranfstab file.

When the oranfstab file is placed in /etc, then it is globally available to all Oracle databases, and can contain mount points used by all Oracle databases running on nodes in the cluster, including single-instance databases. However, on Oracle RAC systems, if the oranfstab file is placed in /etc, then you must replicate the file /etc/oranfstab file on all nodes, and keep each /etc/oranfstab file synchronized on all nodes, just as you must with the /etc/fstab file.

cd /etc

grant root/root access to oranfstab

update oranfstab file with /oradata and /orabkup mount point

Example file provided below for MYDBDEV:

$ORACLE_HOME/dbs/oranfstab or cat /etc/oranfstab

server: stgasm <=== NFS server Host name

path: 10.177.52.158 <--- First path to NFS server ie NFS server NIC

local: 10.177.52.151 <--- First client-side NIC

path: 10.177.52.159 <--- Second path to NFS server ie NFS server NIC (For load balance purpose)

local: 10.177.52.151 <--- Second client-side NIC (For load balance purpose)

export: /oraclenfs mount: /oradata1

server: (Storage server Name)

path: <IP 1.1.1.1>

#path:

export: /vol/<XXXXXXX>: /<MP>

server: (Storage server Name)

path: <IP 1.1.1.1>

#path:

export: /vol/<XXXXXXX>: /<MP>

dontroute

4 Verify mtab

cd /etc

Verify mount point entries on mtab for which you want to setup the DNFS.

Example: MYDBDEV

<Storage host name >:/vol/mydbdev_MP /MP nfs rw,nolock,bg,hard,nointr,rsize=32768,wsize=32768,tcp,nfsvers=3,timeo=600,addr=10.1.1.1 0 0

5 . What is oradism? When is it used by Direct NFS?

Direct NFS client uses oradism executable to perform functions that require root privileges. oradism is configured by default on a single instance and for RAC if not using shared $ORACLE_HOME.

It is used for the following special purposes:

Direct NFS client needs root privileges to start communication with the NFS filer. It uses oradism to obtain the root file handle for the exported volume and the NFS server port and NFS mount port. Once the root handle and port information is obtained, all future communication is issued by Oracle user processes using normal privileges.

Oradism executable need always to be owned by root otherwise instance will not come up and returns the error in alert log:

Direct NFS: please check that oradism is setuid

Verify oradism

cd $ORACLE_HOME/bin

verify the permissions on oradism binary. It should have root oradba grants

If the binary doesn't have the right grants: Follow the steps below

# chown root:oradba oradism / chmod 4755 oradism /cd $ORACLE_HOME/bin/oradism

If ‘oradism’ is not configured correctly, this error in seen the alert log:

“Direct NFS: please check that oradism is setuid”

6. Restart the database server

sudo reboot –p

7. Verify the status of the database

If it's not started. Syntax: sqlplus "/as sysdba" , startup

How can the DNFS usage be verified?

1. Verify the status of DNFS

Vi my_db_dnfs_layout.sql

spool db_dnfs_layout.html

SET MARKUP HTML ON

set echo on

set pagesize 200

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

select 'THIS DB REPORT WAS GENERATED AT: ==)> ' , sysdate " " from dual;

select 'HOSTNAME ASSOCIATED WITH THIS DB INSTANCE: ==)> ' , MACHINE " " from v$session where program like '%SMON%';

select * from v$dnfs_servers;

select * from v$dnfs_files;

select * from v$dnfs_channels;

select * from v$dnfs_stats;

select * from v$datafile;

select * from dba_data_files;

select * from DBA_TEMP_FILES;

select * from v$logfile;

select * from v$log;

select * from v$version;

show parameter

spool off

exit

sqlplus "/as sysdba"

@ my_db_dnfs_layout.sql

2. Just after the initialization parameters are listed in the alert log, you will see the following entry

Direct NFS client is enabled by checking the below message

Oracle instance running with ODM: Oracle Direct NFS ODM Library Version2.0

3. Along with the message from the alert log, this query on v$dnfs_servers ensures that dNFS is truly being used (returns !=0 value):

select count(*) from v$dnfs_servers

( sometimes it isn’t true ;)

4. $ ps -ef | grep dbw

oracle 23126 1 0 06:53 ? 00:00:00 ora_dbw0_MYINST1

oracle 23128 1 0 06:53 ? 00:00:00 ora_dbw1_MYINST1

oracle 23246 22377 0 07:04 pts/0 00:00:00 grep dbw

$ lsof –p < dbw pid >

/usr/sbin/lsof -p 23126

# DNFS OFF

lsof -p 725 | grep data01.dbf

oracle 725 oracle 262u REG ... /nfsimp/data01.dbf (192.168.51.21:/u01)

# DNFS ON

lsof -p 6540 | grep 192.168.51.21

oracle 6540 oracle 32u IPv4 ... TCP dbhost:26171->nfsserver:nfs (ESTABLISHED)

5. alert.log

Direct NFS: channel id [0] path [IPnfs] to filer [KUKARACHA] via local [IPdb] is UP

Direct NFS: channel id [1] path [IPnfs] to filer [KUKARACHA] via local [IPdb] is UP

About DNFS things to remember for knowledge purpose::

1 DNFS oranfstab

Parameter	Usage
Server	Unique identifier for the NFS server
Local	Network paths (up to 4) on the database host
Path	Network paths (up to 4) on the NFS server
Export	The exported volume on the NFS server
Mount	The local mount point for the exported volume
mnt_timeout	Time in seconds to wait for the first mount
dontroute	Prevents OS routing of outgoing messages
management	Network path for NFS server management interface

2 Direct NFS searches for mount entries in the following order

$ORACLE_HOME/dbs/oranfstab # an instance specific

/etc/oranfstab

/etc/mtab

3 You must restart an instance after adjusting oranfstab file

Disadvantages of DNFS ::

· It is relatively difficult to troubleshoot

· You will not find many DNFS messages in alert.log or trace files

· Sometimes Oracle SILINTLY switches from dNFS to kNFS

· This sections gives you useful troubleshooting hints

VVI Note::

Don’t mix old and new methods

● Old method

cd $ORACLE_HOME/lib

mv libodm11.so libodm11.so_bak

ln -s libnfsodm11.so libodm11.so

● New/current method

make -f ins_rdbms.mk dnfs_on

● If you enable dNFS using one method disable it the same way.

ODbaBlogs

Tuesday, July 8, 2014

Work on jobs for expdp an impdp

EXPDP and IMPDP through Network link

Friday, July 4, 2014

Database Health checkup script

Tuesday, July 1, 2014

Configure Direct NFS Client (DNFS) on Linux

Convert snapshot standby database to Physical standby database: Dataguard 11gR2

UNIX Scripts

DB scripts