How to drop other schema’s Database link using SYS user

Many a time we DBA’s face issues as we cannot drop other schema’s database link with schema qulalifier Since dot is allowed in the database link name. Oracle check for a dblink with the given name under the same schema. For performing this task we need to connect to owner schema & drop it.


SQL> select owner,db_link from dba_db_links;
OWNER DB_LINK
------------------------------ --------------------------------------
TEST TEST_DB_LINK
SQL> sho user
USER is "SYS"
SQL>
SQL> drop database link test.test_db_link;
drop database link test.test_db_link
 *
ERROR at line 1:
ORA-02024: database link not found

This post will provide u – small script to drop the database links from SYS, without resetting the schema password or login into the schema.
This will be useful after cloning UAT environment to drop the production links sitting under different schemas.

Contents of scripts are as follows:

[test@test.test.com ~]$ cat drop_schema_dblink.sh
username=$1
db_link=$2
sqlplus /nolog <<!
spool $1_$2_drop_dblink.log
conn / as sysdba
prompt " DB Link Before Drop"
set lines 100
col db_link for a30
select owner,db_link from dba_db_links where owner=upper('$1') and db_link=upper('$2') ;
CREATE or replace PROCEDURE $username.drop_db_link AS
BEGIN
EXECUTE IMMEDIATE 'drop database link $2';
END ;
/
execute $username.drop_db_link;
drop procedure $username.drop_db_link;
prompt " DB Link After Drop"
select owner,db_link from dba_db_links where owner=upper('$1') and db_link=upper('$2') ;
!

Usage of the script is very simple & is as follows:

[test@test.test.com~]$ sh drop_schema_dblink.sh test test_db_link
SQL*Plus: Release 11.2.0.3.0 Production on Sun Jun 29 20:37:04 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> SQL> Connected.
SQL> " DB Link Before Drop"
SQL> SQL> SQL>
OWNER DB_LINK
------------------------------ ------------------------------
TEST TEST_DB_LINK
SQL> 2 3 4 5
Procedure created.
SQL>
PL/SQL procedure successfully completed.
SQL>
Procedure dropped.
SQL> " DB Link After Drop"
SQL>
no rows selected
SQL>
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath

 

Advertisements

Why alert log shows: Diskgroup Gets Mounted / Dismounted

I always see the following messages in Alertlog , was curious to know the reason behind it…


Wed Jun 25 06:35:22 2014
SUCCESS: diskgroup DATA2 was mounted
SUCCESS: diskgroup DATA2 was dismounted
SUCCESS: diskgroup DATA2 was mounted
SUCCESS: diskgroup DATA2 was dismounted
SUCCESS: diskgroup DATA2 was mounted
SUCCESS: diskgroup DATA2 was dismounted
SUCCESS: diskgroup DATA2 was mounted
SUCCESS: diskgroup DATA2 was dismounted
Wed Jun 25 06:35:22 2014
Starting control autobackup
Control autobackup written to DISK device
 handle '/backup/BAK/OMSMIT/cfc-453770213-20140625-01.ctl'
Wed Jun 25 06:35:38 2014
SUCCESS: diskgroup DATA2 was mounted
SUCCESS: diskgroup DATA2 was dismounted
SUCCESS: diskgroup DATA2 was mounted
SUCCESS: diskgroup DATA2 was dismounted
Wed Jun 25 09:34:53 2014

 

According to metalink note : Why Archive, FRA Diskgroup Gets Mounted / Dismounted ? (Doc ID 603204.1)

This is expected behavior. The messages you see are not errors (that is why they are prefixed with “SUCCESS”). The database instance is creating and then closing archive logs one at a time in the FLASHDG_NEW disk group. Whenever a database closes its last file in a disk group, it dismounts the disk group. Whenever a database accesses a disk group when it does not have any other files open in the disk group, it mounts the disk group.

This alerts will be only seen for diskgroups that holds flash recovery area.

If you do not want to see the log messages frequently, you can put a mirrored control-file, or mirrored online redo, or dummy online tablespace data file on the diskgroup. This message will not be output frequently in this case. Because CKPT keeps opening a control-file, LGWR keeps opening an online redo, DBW keeps opening an online tablespace data file. So, the diskgroup is not dismounted until the database shutdown.


Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath

Resolving cursor: pin S wait on X using interesting column final_blocking_session in v$session

Recently I was having issue in our test environment, users were complaining that database is not responding for any queries, it appears to be in hanged state.

After checking database for possible causes for performance issues, I found following:


 

SQL> select event,count(0) from v$session where username is not null and status='ACTIVE' group by event;
event                      count(0)
-----------------------    -----------
db file sequential read    1 
library cache lock         4 
cursor: pin S wait on X    25 
SQL*Net message to client  1

 

Definitely something was wrong with the database. So firstly I concentrated on wait event library cache lock. I found some user was executing following DDL on one of the highly accessible table in the database:


 

SQL> @s1
Enter Session ID: 1409
======================================================================================================
SID/Serial : 1409,39547
Module : TOAD 10.6.1.3
SQL_ID : d489wrxp66a0m
Foreground : PID: 11016:1228 - Toad.exe
Shadow : PID: 12339 - oracle@test4.test.com
Terminal : XXGGG / UNKNOWN
OS User : C_GGG on TEST4\C_GGG2-L
Ora User : SADB
Status Flags: ACTIVE DEDICATED USER
Tran Active : 000000028CC470A0
Login Time : Thu 11:44:52
Last Call : Thu 11:48:59 - 199.7 min
Lock/ Latch : NONE/ NONE
Latch Spin : NONE
Current SQL statement:
 ALTER TABLE sadb.audit_log DROP UNUSED COLUMNS CHECKPOINT 1000
Previous SQL statement:
Session Waits:
 WAITING: db file sequential read
Connect Info:
 : TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
 : Oracle Advanced Security: encryption service for Linux: Version 11.2.0.3.0 - Production
 : Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0.3.0 - Production
Locks:
 TYPE=AE H: S R: NONE - ID1=282717 ID2=0
 DML/DATA ENQ H: X R: NONE - AUDIT_LOG
====================================================================================================

 

So here I got culprit for wait eventlibrary cache lock. Killing this session would have resolved sessions waiting for this wait event.

But what causing cursor: pin S wait on X?

I searched on Google for possible causes of this wait event, I found lots of documentation each pointing to different possibilities.

Is same session with SID : 1409 was the culprit? After describing v$session view, I found very interesting column – ‘final_blocking_session’ – which is introduced in Oracle 11g.


 

SQL> select distinct blocking_session from v$session where event='cursor: pin S wait on X';
BLOCKING_SESSION
----------------
 1305
 1520
SQL> select sql_id from v$session where sid in (1305,1520);
SQL_ID
-------------
4vcd40bta41pb
0h4n33suqpqtv
SQL> select distinct final_blocking_session from v$session where event='cursor: pin S wait on X';
FINAL_BLOCKING_SESSION
----------------------
 1409

 

So I found the culprit :-). After killing session with SID : 1409, issue got resolved.

After searching on Metalink I found very useful note:

Troubleshooting Database Contention With V$Wait_Chains (Doc ID 1428210.1), which says

In 11.2 you can add v$session.final_blocking_session to see the final blocker. The final blocker is the session/process at the top of the wait chain. This is the session/process that maybe causing the problem. Example of query with final_blocking_session info:


 

set pages 1000
set lines 120
set heading off
column w_proc format a50 tru
column instance format a20 tru
column inst format a28 tru
column wait_event format a50 tru
column p1 format a16 tru
column p2 format a16 tru
column p3 format a15 tru
column Seconds format a50 tru
column sincelw format a50 tru
column blocker_proc format a50 tru
column fblocker_proc format a50 tru
column waiters format a50 tru
column chain_signature format a100 wra
column blocker_chain format a100 wra
SELECT * 
FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE, 
 'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'<none>',blocker_osid)|| 
 ' from Instance '||blocker_instance BLOCKER_PROC,
 'Number of waiters: '||num_waiters waiters,
 'Final Blocking Process: '||decode(p.spid,null,'<none>',
 p.spid)||' from Instance '||s.final_blocking_instance FBLOCKER_PROC, 
 'Program: '||p.program image,
 'Wait Event: ' ||wait_event_text wait_event, 'P1: '||wc.p1 p1, 'P2: '||wc.p2 p2, 'P3: '||wc.p3 p3,
 'Seconds in Wait: '||in_wait_secs Seconds,
 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
 'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,
'Blocking Wait Chain: '||decode(blocker_chain_id,null,
 '<none>',blocker_chain_id) blocker_chain
FROM v$wait_chains wc,
 gv$session s,
 gv$session bs,
 gv$instance i,
 gv$process p
WHERE wc.instance = i.instance_number (+)
AND (wc.instance = s.inst_id (+) and wc.sid = s.sid (+)
 and wc.sess_serial# = s.serial# (+))
 AND (s.final_blocking_instance = bs.inst_id (+) 
and s.final_blocking_session = bs.sid (+))
 AND (bs.inst_id = p.inst_id (+) and bs.paddr = p.addr (+))
 AND ( num_waiters > 0
 OR ( blocker_osid IS NOT NULL
 AND in_wait_secs > 10 ) )
ORDER BY chain_id,
 num_waiters DESC)
WHERE ROWNUM < 101;

 

This sql will give u OS process ID for final blocker.


 

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath

UNNAMED datafile issue in standby database: ORA-01111, ORA-01110, ORA-01157

We have following two reasons for getting UNNAMED files created on standby database:

1. STANDBY_FILE_MANAGEMENT enables or disables automatic standby file management. When automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database. Setting this parameter to MANUAL causes MRP process to termainate creating UNNAMED datafile under $ORACLE_HOME/dbs location.
OR
2. We have insufficient space available at mount point /diskgroup of standby database server.


Alert log will show following errors:

MRP0: Background Media Recovery terminated with error 1111
Mon Jun 14 07:52:10 2014
Errors in file /u04/app/oracle/admin/testdr/TEST_DR_dr_srv1/bdump/testdr1_mrp0_28595.trc:
ORA-01111: name for data file 18 is unknown - rename to correct file
ORA-01110: data file 18: '/u01/app/oracle/product/10.2.0/test/dbs/UNNAMED0018'
ORA-01157: cannot identify/lock data file 18 - see DBWR trace file
ORA-01111: name for data file 18 is unknown - rename to correct file
ORA-01110: data file 18: '/u01/app/oracle/product/10.2.0/test/dbs/UNNAMED0018'
Mon Jun 14 07:52:10 2014
Errors in file /u04/app/oracle/admin/fodsdr/FODS_DR_rdr-db1/bdump/testdr1_mrp0_28595.trc:
ORA-01111: name for data file 18 is unknown - rename to correct file
ORA-01110: data file 18: '/u01/app/oracle/product/10.2.0/test/dbs/UNNAMED0018'
ORA-01157: cannot identify/lock data file 18 - see DBWR trace file
ORA-01111: name for data file 18 is unknown - rename to correct file
ORA-01110: data file 18: '/u01/app/oracle/product/10.2.0/test/dbs/UNNAMED0018'
Mon Jun 14 07:52:10 2014
MRP0: Background Media Recovery process shutdown (TESTDR1)

How to resolve:


On standby database run following command to find missing files:

SQL> select FILE#||','||ERROR||','||STATUS||','||RECOVER||','||FUZZY||','||NAME from v$datafile_header where ERROR='FILE MISSING';
FILE#||','||ERROR||','||STATUS||','||RECOVER||','||FUZZY||','||NAME
--------------------------------------------------------------------------------
18,FILE MISSING,ONLINE,,,

SQL> select file#,name from v$datafile where file_id=18;
FILE#  NAME
-----  ---------------------------------------------------------
18     /u01/app/oracle/product/10.2.0/test/dbs/UNNAMED0018

On primary database check for datafile details:

SQL> select FILE_NAME||','||TABLESPACE_NAME||','||FILE_ID||','||STATUS||','||bytes from dba_data_files where file_id = 18;
FILE_NAME||','||TABLESPACE_NAME||','||FILE_ID||','||STATUS||','||BYTES
--------------------------------------------------------------------------------
+DATA1/test/datafile/test.639.756552633,TEST,18,AVAILABLE,32212254720

Change STANDBY_FILE_MANAGEMENT to MANUAL on standby database:

SQL> alter system set standby_file_management=MANUAL sid='*';
System altered.

Now manually create missing datafile on standby database:

SQL> alter database create datafile '/u01/app/oracle/product/10.2.0/test/dbs/UNNAMED0018' as '+DATA1' size 30G;

Now once again change STANDBY_FILE_MANAGEMENT to AUTO on standby database:

SQL> alter system set standby_file_management=AUTO sid=’*’;
System altered.


Now start managed recovery on standby database:

SQL> recover managed standby database disconnect from session;

After this u will see MRP process starts applying archivelog on standby database.


Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath

 

 

ORA-29786: SIHA attribute GET failed with error [Attribute ‘SPFILE’ sts[200] lsts[0]]

Recently while performing fresh ASM installation, I was trying to create spfile from pfile , I encountered this issue:

ORA-29786: SIHA attribute GET failed with error [Attribute ‘SPFILE’ sts[200] lsts[0]]


 

[oraasm@rmb-put-mordor ~]$ sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 6 07:55:51 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: / as sysasm
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option
SQL> sho parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL>
SQL> create spfile from pfile;
create spfile from pfile
*
ERROR at line 1:
ORA-29786: SIHA attribute GET failed with error [Attribute 'SPFILE' sts[200]
lsts[0]]

After searching on metalink I found that ASM database was not started using SVRCTL utility, It was started manually.


 

[oraasm@rmb-put-mordor ~]$ srvctl status asm
ASM is not running.

To resolve this issue , we need to start ASM using SRVCTL utility. Please find below to resolve this issue:


 

[oraasm@rmb-put-mordor ~]$ srvctl add asm
[oraasm@rmb-put-mordor ~]$ srvctl status asm
ASM is not running.
[oraasm@rmb-put-mordor ~]$ srvctl start asm
[oraasm@rmb-put-mordor ~]$ srvctl status asm
ASM is running on rmb-put-mordor
[oraasm@rmb-put-mordor ~]$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 6 07:59:44 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option
SQL>
SQL> sho parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL>
SQL> create spfile from pfile;
File created.
SQL>
SQL> shu immediate
ASM diskgroups dismounted
ASM instance shutdown
SQL> startuP
ASM instance started
Total System Global Area 684785664 bytes
Fixed Size 2231512 bytes
Variable Size 657388328 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
SQL> sho parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/oracle/ASM/11.2.0/grid/dbs/spfile+ASM.ora
SQL>

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath

Slow Materialized View Complete Refresh Issues Resolved….

I was recently working on tuning – production environment, which had number of materilazed views which were scheduled to complete refresh during off hours.

This complete refresh process was very time consuming, also producing a large amount archivelogs & undo.

Customer was complaning about sudden change in materialed view behaviour, after they upgraded database from 9i to 11g.

After reading Oracle documentation about materialized views I found, the reason for this sudden behavoiur change.


 

From 10g, Oracle has changed the default parameter value of ATOMIC_REFRESH in the DBMS_MVIEW.REFRESH package.

In earlier releases (before 10g) the parameter was set to FALSE by default but now it is set to TRUE, which forces a DELETE of the materialized view instead of TRUNCATE, making the materialized view more “available” at refresh time.


 

Advantages of ATOMIC_REFRESH => FLASE

  • No or very less archive generation
  • Complete refresh process is very fast as compared to process with ATOMIC_REFRESH => TRUE

Disadvantages of ATOMIC_REFRESH => FLASE

  • Unavailability of data during the refresh process, which could be unacceptable to application and business user.

 

After discussing with customer, I made changes in refresh methodology so as to use ATOMIC_REFRESH => FLASE.


 

          Time Taken          Archive generation
Before    Approx. 43 minutes  Approx. 68 
After     Approx. 6 minutes   Approx. 7

Usage:

EXEC DBMS_MVIEW.REFRESH(‘MVIEW1’, METHOD => ‘C’, ATOMIC_REFRESH => FALSE);

Hope so this post is useful for you 🙂

Cheers

Regards,

Adityanath

DBCA failing with ORA-01034

I faced this issue while creating database using DBCA in one of the UAT database server. Issue was with just DBCA , I was able to create database manually without any issues.

Each attempt of creating database with DBCA utility was failing with ORA-01034: ORACLE not available.

After putting lots of efforts, I started concentrating on error itself ORA-01034: ORACLE not available. 

What is the possibility of getting ORA-01034 in normal scneario:

  •  Database is down & u r trying execute some sql statement on it

Simple… I am creating my database, means database is not open, & what is the possibility of someone executing any query on my database….

one and only glogin.sql is the culprit.

What Oracle documentation says about glogin.sql

There’s a script in ORACLE_HOME/sqlplus/admin which is called glogin.sql.  This script is automatically run when the user starts SQL*PLUS or uses the Connect command, hence any command added to it will be executed. 

I found following sql was present in glogin.sql:

select name,open_mode from v$database;

After removing all entries from glogin.sql, I was able to create database using DBCA without issues.

..

Hope so this post will be useful for u 🙂

Cheers

Regards,

Adityanath