Archive for the ‘ Oracle DBA ’ Category

Execution Plans

Advertisements

Creating Local Yum in Oracle Linux 6.3

Manual installation of Oracle required Packages is always frustrating one while installing Oracle Database in a Linux Box. Oracle Linux came up with a package named oracle-validated-preinstall to manage all installation prerequisites easily. To take advantage of it, you can configure local yum using those following steps.

First of all, create a empty directory and copy all packages from CD or ISO Image.
Continue reading

ORA-12528 tns listener all appropriate instances are blocking new connections

Scenario:


SQL> set ORACLE_SID=ORCL
SQL> startup nomount
ORACLE instance started.

Total System Global Area  263639040 bytes
Fixed Size                  1332552 bytes
Variable Size             222300856 bytes
Database Buffers           33554432 bytes
Redo Buffers                6451200 bytes

C:\>lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production 

Service "ORCL" has 2 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:59
         LOCAL SERVER
  Instance "ORCL", status BLOCKED, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "ORCL_XPT" has 1 instance(s).
  Instance "ORCL", status BLOCKED, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER

Want Solution Do the following : Continue reading

Windows Batch Script for Datapump Backup

Following is the content Batch Script for taking datapump backup in Windows OS.


set mydate=%date:~-7,2%%date:~-10,2%%date:~-4,4%%time:~-11,2%%time:~-8,2%%time:~-5,2%%time:~-2,2% 
expdp system/system schemas=orbhrm directory=BACKUP_DIR dumpfile=orbhrm%mydate%.dmp logfile=orbhrm%mydate%.log exclude=TABLE:\"IN \(\'RCAPPDTL\'\)\" 

Thanks
A Rahim Khan

rman backup on disk script oracle database

Following is the content of offshore_rman.sh for taking rman level 0 backup on sever disk.


mkdir -p /software/rmantemp/offshore_`date +%d-%m-%y`
chmod g+w /software/rmantemp/offshore_`date +%d-%m-%y`

export ORACLE_HOME=/oracle/app/product/11.2.0/db_1
export ORACLE_SID=offshore

$ORACLE_HOME/bin/rman target / <<!

CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/software/rmantemp/offshore_`date +%d-%m-%y`/OFFSHORE_DB_CTL_%F'; 
RUN { 
SQL 'ALTER SYSTEM SWITCH LOGFILE'; 
BACKUP INCREMENTAL LEVEL 0 DATABASE FORMAT '/software/rmantemp/offshore_`date +%d-%m-%y`/OFFSHORE_DB_DATAFILE_%u_%s_%p.DBF' TAG 'DATAFILE_BACKUP'; 
BACKUP ARCHIVELOG ALL FORMAT '/software/rmantemp/offshore_`date +%d-%m-%y`/OFFSHORE_DB_ARCHIVELOG_%u_%s_%p.DBF' TAG 'ARCHIVELOG_BACKUP'; 
DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE -5'; 
} 
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;


Thanks
A Rahim Khan

Changing Oracle Snapshot Interval and Retention

to check out snapshot settings


select * from dba_hist_wr_control;
--------------------------------------------
DBID:           1225231090
SNAP_INTERVAL:  +00 01:00:00.000000
RETENTION:      +35 00:00:00.000000
TOPNSQL:        DEFAULT


to change snapshot interval and retention period


execute dbms_workload_repository.modify_snapshot_settings( interval => 60, retention => 50400);


to determine appropriate size sysaux table space


/oracle/app/11g/db_1/rdbms/admin/utlsyxsz.sql

Thanks
A Rahim Khan

Temporarily disable data guard using data guard broker

Those are some handy dgmgrl Commands to stop Log Transport in Primary Database and Log Apply in Standby Database.


bash-3.2$ dgmgrl
DGMGRL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.
DGMGRL> show configuration

Configuration - TESTADG

  Protection Mode: MaxPerformance
  Databases:
    prim - Primary database
    stnd - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Continue reading