Oracle DBA Tips and Techniques

By Arjun Raja

Archive for January, 2011

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 »

Block Change Tracking file error – ORA-19750 during RECOVERY.

Posted by Arjun Raja on January 18, 2011

While restoring and recovering the a database which uses the block change tracking file we faced a documented problem with the block_change_tracking file not being avaiblable.

This was the error during RECOVERY….

released channel: ch1
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 02/18/2009 09:33:29 ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile
‘/u02/oradata/flash_recovery_area/XXXXXXX/archivelog/2009_02_18/o1_mf_1_117043_4spoq75y_.arc’ ORA-00283: recovery session canceled due to errors
ORA-19755: could not open change tracking file
ORA-19750: change tracking file:
‘/u02/oradata/xxxxxx/xxxxxx_blk_change.dbf’
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory Additional information: 3

This Block Change Tracking file is not backed up by RMAN and not needed for recovery – it is purely used by RMAN for speeding up the incremental backups.

Therefore to workaround this problem simply disable block_change_tracking and restart the recovery .

sql > alter database disable block change tracking ;

RMAN> run {
SET UNTIL TIME “TO_DATE (’09-02-09 18:00:00′, ‘DD-MM-YY HH24:MI:SS’)”; allocate channel ch1 type ‘sbt_tape’ parms ‘ENV=
(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)’; recover database;
release channel ch1 ;
}
executing command: SET until clause

allocated channel: ch1
channel ch1: sid=310 devtype=SBT_TAPE
channel ch1: Data Protection for Oracle: version 5.4.1.0

Starting recover at 18-FEB-09

starting media recovery

archive log thread 1 sequence 117043 is already on disk as
file /u02/oradata/flash_recovery_area/XXXXXXXX/archivelog/2009_02_18/o1_mf_1_117043_4spoq75y_.arc archive log thread 1 sequence 117044 is already on disk as
file /u02/oradata/flash_recovery_area/XXXXXXXXarchivelog/2009_02_18/o1_mf_1_117044_4spoq6n3_.arc archive log
filename=/u02/oradata/flash_recovery_area/XXXXXX/archivelog/2009_02_18/o1_mf_1_117043_4spoq75y_.arc thread=1 sequence=117043
archive log
filename=/u02/oradata/flash_recovery_area/XXXXX/archivelog/2009_02_18/o1_mf_1_117044_4spoq6n3_.arc thread=1 sequence=117044
archive log
filename=/u02/oradata/flash_recovery_area/XXXXX/archivelog/2009_02_18/o1_mf_1_117045_4spoq7ks_.arc thread=1 sequence=117045
channel ch1: starting archive log restore to default destination ==

Recovery continues.

Posted in Uncategorized | 1 Comment »

Avoid ORA -27123 error

Posted by Arjun Raja on January 18, 2011

To avoid ORA -27123 error while accessing database while logged in as another user into the unix machine other than oracle software owner.

Ex – Login as gentest into a machine where the software is owned by oracle user.

gentest@xxxxxxxxxs password:
gentestxxxxxxxx % sqlplus /

SQL*Plus: Release 10.2.0.3.0 – Production on Wed Dec 3 11:11:17 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27123: unable to attach to shared memory segment
IBM AIX RISC System/6000 Error: 13: Permission denied

So login as owner oracle –

cd $ORACLE_HOME/bin

ls -l oracle

-rwxr-xr-x 1 oracle oinstall 131619948 May 31 2007 oracle

chmod ug+s oracle

oracle@ bin> ls -l oracle
-rwsr-sr-x 1 oracle oinstall 131619948 May 31 2007 oracle oracle@ bin>

Now as gentest user it works….

gentest@xxxxxxx) %
gentest@xxxxxxxx % sqlplus /

SQL*Plus: Release 10.2.0.3.0 – Production on Wed Dec 3 11:19:14 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL>

Posted in Uncategorized | Leave a Comment »

CLEAR RMAN CONFIGURATION

Posted by Arjun Raja on January 18, 2011

If you need to clear one of the parameters in an RMAN configuration, use the CLEAR command.

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO ‘SBT_TAPE’;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO ‘%F’; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ MAXPIECESIZE 65 G;
CONFIGURE MAXSETSIZE TO 65 G;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO
‘/u01/app/oracle/product/10.2/dbs/snapcf_ofsam.f’; # default

RMAN> CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ CLEAR;

old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ MAXPIECESIZE 65 G;
old RMAN configuration parameters are successfully deleted
released channel: ORA_SBT_TAPE_1

RMAN> SHOW ALL;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO ‘SBT_TAPE’;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO ‘%F’; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO 65 G;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO
‘/u01/app/oracle/product/10.2/dbs/snapcf_ofsam.f’; # default

RMAN>

CHANNEL DEVICE TYPE ‘SBT_TAPE’ MAXPIECESIZE 65 G;- CLEARED !

Posted in Uncategorized | Leave a Comment »

TSM BACKUP TROUBLESHOOTING

Posted by Arjun Raja on January 18, 2011

RMAN- BACKUP USING TSM- TROUBLE SHOOTING :

If backup to tape fails at first, it may be because the environmnent is not picking up a library file required….

run {
allocate channel ch1 device type sbt parms ‘ENV=
(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)’; backup current controlfile;
}

RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of allocate command on ch1 channel at 07/21/2008 11:17:14
ORA-19554: error allocating device, device type: SBT_TAPE, device name: ORA-27211: Failed to load Media Management Library
Additional information: 2

RMAN> exit

cd $ORACLE_HOME/lib

libobk* not found
apex:/u01/oracle/product/11.0/lib> ln -s /usr/lib/libobk64.a libobk.a apex:/u01/oracle/product/11.0/lib> ls -lrt libobk*
lrwxrwxrwx 1 oracle dba 19 Jul 21 11:32 libobk.a -> /usr/lib/libobk64.a

apex:/u01/oracle/product/11.0/lib>export
TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt

apex:/u01/oracle/product/11.0/lib> sbttest test
The sbt function pointers are loaded from libobk.a(shr.o) library.

Posted in Uncategorized | 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 »