Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Wednesday, March 31, 2010

Change listener log

UNIX> cd /u0001/oracle/product/10.2.0/network/log
UNIX> lsnrctl set log_status off
UNIX> mv listener.log listener.old
UNIX> lsnrctl set log_status on

Tuesday, March 30, 2010

Restore Archivelog with Particular sequence

RMAN> run {
2> allocate channel dsk1 type disk;
3> restore archivelog logseq 11645;
4> release channel dsk1;
5> }

what if it is a RAC?
What if particular logseq and thread?

RMAN> run {
2> allocate channel ch01 type disk format '/ora_backup/u0006/FIPRD3/archivelog/';
3> restore archivelog logseq 24208 thread 3;
4> }

using target database control file instead of recovery catalog
allocated channel: ch01
channel ch01: sid=639 instance=FIPRD3 devtype=DISK

Starting restore at 19-APR-11

channel ch01: starting archive log restore to default destination
channel ch01: restoring archive log
archive log thread=3 sequence=24208
channel ch01: reading from backup piece /ora_backup/u0006/FIPRD3/archivelog/ARC_FIPRD3_20110417_115101_s298206_p1_6uma0fad
channel ch01: restored backup piece 1
piece handle=/ora_backup/u0006/FIPRD3/archivelog/ARC_FIPRD3_20110417_115101_s298206_p1_6uma0fad tag=TAG20110417T115252
channel ch01: restore complete, elapsed time: 00:00:05
Finished restore at 19-APR-11
released channel: ch01



restoring to a different archivelog destination than default and with particular thread log sequence:

RMAN> run{
2> set archivelog destination to '/ora_backup/u0001/cloning/';
3> restore archivelog sequence 6232 thread 3;
4> }

executing command: SET ARCHIVELOG DESTINATION
using target database control file instead of recovery catalog

Starting restore at 26-JUN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2610 instance=ICMPRD3 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=948 instance=ICMPRD3 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=2365 instance=ICMPRD3 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=3315 instance=ICMPRD3 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=1188 instance=ICMPRD3 device type=DISK

channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/ora_backup/u0001/cloning/
channel ORA_DISK_1: restoring archived log
archived log thread=3 sequence=6232
channel ORA_DISK_1: reading from backup piece /ora_backup/u0004/ICMPRD3/archivelog/ARC_ICMPRD3_20110624_130406_s7190_p1_0mmfmh90
channel ORA_DISK_1: piece handle=/ora_backup/u0004/ICMPRD3/archivelog/ARC_ICMPRD3_20110624_130406_s7190_p1_0mmfmh90 tag=TAG20110624T130521
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 26-JUN-11

Monday, March 29, 2010

Avoid RMAN-08137

I used the below script to delete the archive logs older than 30 mins so that the recent archivelogs will be available to the standby and over come the below error messages:

RMAN-08137: WARNING: archive log not deleted as it is still needed


RUN{
ALLOCATE CHANNEL ch01 TYPE DISK FORMAT '/u0001/${ORACLE_SID}/u01/%d_${BACKUP_TYPE}_%M_%D_%Y_%s_%p_%t';

BACKUP
TAG 'arch_${TODAY}_${TIME}'
FILESPERSET ${ARCHIVE_PERSET}
ARCHIVELOG ALL;
DELETE force noprompt archivelog until time '(sysdate -1/30)' backed up 1 times to device type disk;
RELEASE CHANNEL ch01;

}
EOF

Wednesday, March 10, 2010

RMAN LIST

These variables determine the format used for the time
parameters in RMAN commands such as RESTORE, RECOVER, and REPORT
export NLS_LANG=american
export NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'

RMAN> LIST BACKUP;
RMAN> LIST BACKUP OF DATABASE;
RMAN> LIST BACKUP OF DATABASE BY BACKUP;
RMAN> LIST BACKUP RECOVERABLE;
RMAN> LIST BACKUP SUMMARY;
RMAN> LIST INCARNATION;
RMAN> LIST BACKUP BY FILE;
RMAN> LIST COPY OF DATABASE ARCHIVELOG ALL;
RMAN> LIST COPY OF DATAFILE 1, 2, 3;
RMAN> LIST BACKUP OF DATAFILE 11 SUMMARY;
RMAN> LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 1437;
RMAN> LIST CONTROLFILECOPY "/tmp/cntrlfile.copy";
RMAN> LIST BACKUPSET OF DATAFILE 1;
RMAN> LIST backup tag '04_12_09_F';
RMAN> LIST BACKUPPIECE ' ';
RMAN> LIST BACKUP OF ARCHIVELOG ;
RMAN> list backup of database completed between "to_date('06/17/2010','mm/dd/yyyy')" and "to_date('06/19/2010','mm/dd/yyyy')";
RMAN> LIST backup of database completed after "to_date('06/20/2010','mm/dd/yyyy')";


Direct the RMAN output to a LOG:

The following example appends the output from an RMAN session to a text file at
/tmp/msglog.log
% rman TARGET / LOG /tmp/msglog.log APPEND

UNIX$RMAN | tee rman_output.log

Thursday, March 4, 2010

Copy ASM files to File system

Use this with the below for clone :
SQL>ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
SQL>create pfile from spfile;


Method-1:
SQL>ALTER DATABASE MOUNT;
RMNA> Connect target /
RMAN> backup as copy tablespace system format '/unix02/u0001/datafilesbackup/%U';
RMAN> backup as copy tablespace sysaux format '/unix02/u0001/datafilesbackup/%U';
exit;

Tuesday, March 2, 2010

DRM - Dynamic Resource Mastering

DRM - Dynamic Resource Mastering

When using Real application Clusters (RAC), Each instance has its own SGA and buffer cache. RAC will ensure that these block changes are co-ordinated to maximize performance and to ensure data intergrity. Each copy of the buffer also called as a cache resource has a master which is one of the nodes of the cluster.

In database releases before 10g (10.1.0.2) once a cache resource is mastered on an instance, a re-mastering or a change in the master would take place only during a reconfiguration that would happen automatically during both normal operations like instance startup or instance shutdown or abnormal events like Node eviction by Cluster Manager. So if Node B is the master of a cache resource, this resource will remain mastered on Node B until reconfiguration.

10g introduces a concept of resource remastering via DRM. With DRM a resource can be re-mastered on another node say from Node B to Node A if it is found that the cache resource is accessed more frequently from Node A. A reconfiguration is no longer the only reason for a resource to be re-mastered.

In 10gR1 DRM is driven by affinity of files and in 10gR2 it is based on objects.

Associated Bug with DRM on 10g:
Instance will terminate with the below error in alert log:

ORA-00481: LMON process terminated with error
also

Instance fails during DRM with lmon reporting kjfcdrmrfg: SYNC TIMEOUT (522650, 521749, 900), step 34
and alert log showing:
Fri Feb XX 06:02:22 2010
Errors in file /bdump/sid1_lmon_1085658.trc:
ORA-00481: LMON process terminated with error

AWR reports shows:

Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
gc remaster 471 97.88 898 1908 0.22


FIX :--

There is not currently a patch for bug 6960699 on AIX.
The workaround is to disable DRM by setting on all instances:
_gc_affinity_time=0
_gc_undo_affinity=FALSE

Find OSPID for SID

select vs.sid,vs.serial#,vs.username,vs.inst_id,vp.spid from gv$session vs,gv$process vp where vs.paddr=vp.addr and sid=1

To view NIC info

How to find what are all NIC's AVAILABLE TO YOU:

Solaris & AIX :
ifconfig
Linux RHES: run as root;
ifconfig -a

Oracle listener established connections

unix $> netstat -an | grep ESTABLISHED | wc -l

gives the count of number of connections being established via listener