The long long route to Kerberos

If you want to single-sign-on to your database with your Windows credentials, be aware, it is hard! But the benefit is quite valuable, no more saved password on the client, central password management and user expiration, compliance to the security guidelines, and at no extra cost Landscape for my setup One PC with Windows (PC01.EXAMPLE.COM) […]

Changing the log apply delay (DelayMins)

Whenever you change the DelayMins setting in Dataguard, you must remember it affects only logs that have not been shipped yet. DGMGRL> show database sDB01 delaymins   DelayMins = '5' DGMGRL> edit DATABASE sDB01 set property delaymins=2; Property "delaymins" updated ARC3: Archive log thread 1 sequence 3199 available in 5 minute(s) Tue Dec 03 15:34:59 2013 […]

sqlnet.ora, sqlplus.exe and tnsping.exe inconsistencies

if you use tnsping.exe and sqlplus.exe, the way the sqlnet.ora and tnsnames.ora are located differs Let’s take the following setup C:\tmp>type dir1\sqlnet.ora NAMES.DEFAULT_DOMAIN = (EXAMPLE.COM) NAMES.DIRECTORY_PATH = (TNSNAMES) C:\tmp>type dir1\tnsnames.ora db.example.com=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv1.example.com)(PORT=1521))(CONNECT_DATA=(SID=db01))) db.example.org=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv1.example.org)(PORT=1521))(CONNECT_DATA=(SID=db01))) C:\tmp>type dir2\sqlnet.ora NAMES.DEFAULT_DOMAIN = (EXAMPLE.ORG) NAMES.DIRECTORY_PATH = (TNSNAMES) C:\tmp>type dir2\tnsnames.ora db.example.com=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv2.example.com)(PORT=1521))(CONNECT_DATA=(SID=db02))) db.example.org=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv2.example.org)(PORT=1521))(CONNECT_DATA=(SID=db02))) You set TNS_ADMIN to dir1 and your current directory is dir2. […]

Monitoring details on your explain plan

We know how to generate an explain plan. SET AUTOT ON, the good old ambulance in Toad (which is no longer an ambulance those days), the explain plan button in SQL Developer or simply EXPLAIN PLAN create table t1 partition by hash(object_id) partitions 16 as select * from dba_objects; exec dbms_stats.gather_table_stats(user, 'T1') explain plan for […]

When v$session_longops is not long enough

With large table scans, sometimes the estimated total work is far beyond reality SQL> select message from v$session_longops where target='SCOTT.EMP'; MESSAGE ———————————————————— Table Scan:  SCOTT.EMP: 7377612 out of 629683 Blocks done The total work is the Oracle estimation : SQL> select blocks from dba_tables where table_name='EMP';     BLOCKS ———-     629683 This may differ quite a lot from […]

How big was my database last month

If you backup your database at least weekly, you could check the datafile size from your backup history. Without catalog : select to_char(d,'"W"IW-IYYY') compl, sum(b)/1024/1024/1024*8192 GB from (   select max(DATAFILE_BLOCKS) b, trunc(completion_time,'IW') d   from v$backup_datafile   group by FILE# ,trunc(completion_time,'IW') ) group by d order by d; COMPL        GB ——– —— W30-2012   3.73 W31-2012   4.84 W32-2012   5.00 […]

How big was my table yesterday

Oracle saves a lot of information on your behalf. Whenever you get yourself an AWR reported, you access some historic tables (included in the Diagnostic Pack). Those tables could also be accessed manually. SELECT savtime,owner,object_name,rowcnt,blkcnt FROM sys.WRI$_OPTSTAT_TAB_HISTORY w,   dba_objects o WHERE    o.owner='SCOTT'    AND o.object_name='EMP'    and o.object_id = W.OBJ# ORDER BY o.owner, o.object_name, […]

tnsping and instant client

Mostly when you install your instant client, you will not have tnsping handy. You could well try to copy it from a full client, but this is cumbersome to just ping your instance. I just created one function in my .profile whence tnsping >/dev/null 2>&1 ||   tnsping() {     sqlplus -L -s x/x@$1 </dev/null |       grep […]

Create database link and logon trigger

Today I could not understand why I was getting ORA-1031 with create database link. After analysis, I found out a logon trigger that changed the current schema. As Tom always says, triggers are evil… DEMO: SQL> create user u1 identified by xxx; User created. SQL> grant create session, create database link to u2 identified by […]

Drop database link in another schema

Today I wrote this script : drop_database_link.sql accept owner char prompt "Enter database link owner : " accept db_link char prompt "Enter link name : " begin   dbms_scheduler.create_job(     job_name=>'&owner..drop_database_link',     job_type=>'PLSQL_BLOCK',     job_action=>'BEGIN execute immediate ''drop database link &db_link'';END;'   );   dbms_scheduler.run_job('&owner..drop_database_link',false);   dbms_lock.sleep(2);   dbms_scheduler.drop_job('&owner..drop_database_link'); end; / I am using the scheduler to run a job as another user. […]

shutdown timeout

I do not like shutdown abort (see this post). I always use shutdown immediate and it always work… well almost always. Today I discovered a 9iR2 new feature : shutdown timeout ! Shutdown Timeout If all events blocking the shutdown do not occur within one hour, the shutdown operation aborts with the following message: ORA-01013: […]

grant select on sys tables

I prefer to use a powerful named user with dba rather than sys. It is more conform to the security policies in place regarding accounting of administrator operations. Very occasionaly, my user get ORA-1031 insufficient privileges even if I have the dba role. Amoung others, I have “PURGE DBA_RECYCLEBIN” and DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE update, 2012-07-24 For purge […]

What does # mean in sqlplus?

The script used to be shutdown abort it has been replaced by #shutdown abort shutdown immediate Let’s try ! SQL> #shutdown abort ORACLE instance shut down. SQL> shutdown immediate ORA-01012: not logged on sqlplus just silently ignored the # symbol and executed the first statement. Thanks to Maxim comment, here is a new case to […]

How to get rid of corrupted blocks without a backup

First, you identify the blocks in alert log or with db verify $ dbv BLOCKSIZE=8192 file=sysaux01.dbf DBV-00201: Block, DBA 12629823, marked corrupt for invalid redo application … DBVERIFY – Verification complete Total Pages Examined         : 131072 Total Pages Processed (Data) : 69691 Total Pages Failing   (Data) : 0 Total Pages Processed (Index): 28669 Total Pages […]

Transport tablespace over db links

You do not want to export the metadata from the source database, but rather use a database link to get this. As prerequisite, you have made a set of self-contained tablespaces in read-only mode and you have copied the datafiles. SQL>  create tablespace test datafile '/u02/oradata/db01/test01.dbf' size 10m; Tablespace created. SQL> create table scott.x(x number) tablespace […]

Failover to standby with a delay (until time)

Disaster scenario : 1) you have a primary database 2) you have a standby database 3) you want to be able to failover to the standby database until a given time (ex: within the last 24 hours) First, you create your primary and standby databases. If you have dataguard broker, you then set the DELAYMINS […]

delete unused shared memory segments from an Oracle instance

Once upon a time, a dba issues some kill -9 to clean up dying database processes. Or the database instance crashes. This will left some shared memory segments. Note 68281.1 describe how to remove them on a server with multiple databases. First, list the ipc process $ ipcs IPC status from /dev/mem as of Mon […]

Generate network graph from command line

I recently wrote on gnuplot, today I tried another command line utility to generate graphs, graphviz, version 2.24.0 on AIX5L. Pretty straightforward syntax : (   echo "digraph Emp {"     sqlplus -s -L scott/tiger << EOF       set pages 0 lin 120 hea off feed off       select         ename ||'->'||         (select ename from emp where empno=e.mgr) || ';' […]

On the number of installed components

I recently posted about network fained fine grained security. More precisely, I posted about the new requirement to have XDB to be able to send a mail or do a nslookup on 11g. What option should you install on your database ? SQL> select COMP_NAME,VERSION from DBA_REGISTRY; COMP_NAME                                VERSION —————————————- ———- Oracle Database Catalog Views            11.2.0.2.0 Oracle […]

how to run UTL_TCP, UTL_SMTP and the like in 11g

After we upgrade a db to 11g someone complained about an ORA-24248: XML DB extensible security not installed I thought, it should be easy to revert to 10g mechanism. Probably wrong after reading Marco : The default behavior for access control to network utility packages has been changed to disallow network operations to all nonprivileged […]

Do not upgrade 11.2.0.1 to 11.2.0.1

If you do run @?/rdbms/admin/catupgrd for an 11.2.0.1 Oracle Home on a 11.2.0.1, you may later realize some objects are missing (probably related to deferred segment creation). SQL>   delete from t1   2    where id in (   3      select ca.id from ca, p   4      where p.no_form like '%02.98'   5      and p.id = ca.prod_id   6    );   delete from t1               * ERROR at […]

How to change the connection string of the Oracle Enterprise Manager Grid Control 11g repository

If you moved your repository to a new host and want to change the connection string, no need to drop it, no need to messup in the properties or xml files, simply read the doc http://download.oracle.com/docs/cd/E11857_01/em.111/e16790/ha_agent.htm#autoId13 emctl config oms -store_repos_details (-repos_host <host> -repos_port <port> -repos_sid <sid> | -repos_conndesc <connect descriptor>) -repos_user <username> [-repos_pwd <pwd>] [-no_check_db] […]