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

Data File Rename/Move in Oracle Active Data Guard Environment

Data File rename or move activity is not applied automatically to standby Database even if STANDBY_FILE_ MANAGEMENT is set to AUTO. When STANDBY_FILE_MANAGEMENT initialization parameter to AUTO, use of the following SQL statements is not allowed:


ALTER DATABASE RENAME .. 
ALTER DATABASE ADD/DROP LOGFILE ..
ALTER DATABASE ADD/DROP STANDBY LOGFILE MEMBER ..
ALTER DATABASE CREATE DATAFILE AS ..

Continue reading

Enable Password Never Expire feature in Oracle

First of all, detect profile name of the target user to set password never expire.


SQL> select profile from DBA_USERS where upper(username) = upper('rman');

PROFILE
------------------------------
DEFAULT

Modify target user profile using this .


SQL> alter profile DEFAULT limit password_life_time UNLIMITED;

Profile altered.

Now, check out user expiry_date.


SQL> select username,expiry_date,account_status from dba_users where upper(username) = upper('rman');

USERNAME                       EXPIRY_DA ACCOUNT_STATUS
------------------------------ --------- --------------------------------
RMAN                                     OPEN


Thanks
A Rahim Khan

Follow

Get every new post delivered to your Inbox.

Join 29 other followers