Category Archives: dba

ps -ef |grep pmon alternative

I just wrote a new alias to check if the databases are up and running. I added the version and a dash for non-running database. Needed is /etc/oratab + sysdba access to the database.

Here it is :

awk -F: ‘/^[^*# ]/{system(“echo 42select 47+ “$1″ 1147||version from v\\$instance;42|ORACLE_SID=”$1″ ORACLE_HOME=”$2″ “$2″/bin/sqlplus -s 42/ as sysdba42 2>/dev/null|grep 42^+42||echo 42- “$1″42″)}’ /etc/oratab

+ LSC01         10.2.0.2.0
+ LSC02         9.2.0.8.0
- LSC03

Or, as an alias :

alias oraver=’awk -F: ‘\”/^[^*# ]/{system(“echo 42select 47+ “$1″ 1147||version from v\\$instance;42|ORACLE_SID=”$1″ ORACLE_HOME=”$2″ “$2″/bin/sqlplus -s 42/ as sysdba42 2>/dev/null|grep 42^+42||echo 42- “$1″42″)}’\” /etc/oratab’

rac automation

Werner Puschitz talked yesterday about Automated Oracle Real Application Clusters Deployment: How Dell Does IT.
The result of the automation is, install a 6 nodes cluster in 30 minutes. Which is quite impressive.

Werner divided the automation in three RPM packages.

1) configure the host: set up private and virtual interface (according to a naming convention, for example private is hostname-priv, vip is hostname-vip). Set up the system configuration (shared memory, etc). Set up the SAN, shared devices. Set up ssh. Define a cronjob to check if all nodes are configured and accessible. Write an email to the sysadmin when all nodes are ready

2) install the clusterware. the RPM is build with a response file + the oracle software. The response file is recorded with ./runInstaller -record.

3) install the database software. the RPM is build with a response file too

Werner said it is rather worth to build those packages for large companies, where you have many RAC installations. In my opinion, even if you have only one or two rac, you can benefit from this. It provides you an easy way to reinstall the system, upgrade it to a new version, create two systems (dev+prod) with the same configuration.

Thanks Werner for this very good one!

NLS_LANG=german_switzerland

There is a bug about NLS_LANG and SWITZERLAND in 10g.

This is because the tausend separator has changed from 9i (.) to 10g (‘).

Check Note 4598613.8

The workaround is to not use NLS_LANG=german_switzerland, but if you have different NLS parameters on the client and on the server, than you will have other problems, ex: questionable statistics by exp/imp.

OK, here is the bug :


$ export NLS_LANG=german_switzerland.we8iso8859p1
$ sqlplus -L scott/tiger
 
SQL*Plus: Release 10.2.0.2.0 - Production on Do Sep 21 13:50:05 2006
 
Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.
 
ERROR:
ORA-00604: Fehler auf rekursiver SQL-Ebene 1
ORA-02248: Ungültige Option für ALTER SESSION
 
 
SP2-0751: Anmeldung bei Oracle nicht möglich. SQL*Plus wird beendet

and my workaround


$ export NLS_LANG="" NLS_TERRITORY=switzerland NLS_LANGUAGE=german
$ sqlplus -L scott/tiger                                                                                                               
SQL*Plus: Release 10.2.0.2.0 - Production on Thu Sep 21 13:51:30 2006
 
Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 
With the Partitioning and Data Mining options

SQL> select * from NLS_SESSION_PARAMETERS ;
PARAMETER              VALUE
---------------------- -----------
NLS_LANGUAGE           GERMAN
NLS_TERRITORY          SWITZERLAND
NLS_CURRENCY           SFr.
NLS_ISO_CURRENCY       SWITZERLAND
NLS_NUMERIC_CHARACTERS .'
NLS_CALENDAR           GREGORIAN
NLS_DATE_LANGUAGE      GERMAN
NLS_SORT               GERMAN
NLS_DUAL_CURRENCY      SF

17 Zeilen ausgewahlt.

SQL> select to_char(1000,'9G999') from dual;
 
TO_CHA
------
 1'000

SQL> quit
Verbindung zu Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
With the Partitioning and Data Mining options beendet

So it is possible to use german_switzerland. Not sure if this workaround is bullet-proof. Metalink says it is fixed in 11g
[edit]fixed in 10.2.0.3 and later [/edit]

generate dml

I just discovered that cool new function in SQL/Developer

just right click on the table and chose export sql insert


-- INSERTING into EMP
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
  "HIREDATE","SAL","COMM","DEPTNO") values (7369
  ,'SMITH','CLERK',7902,to_date('1980-12-17',
  'DD-MON-RR'),800,null,20);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
  "HIREDATE","SAL","COMM","DEPTNO") values (7499
  ,'ALLEN','SALESMAN',7698,to_date('1981-02-20',
  'DD-MON-RR'),1600,300,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
  "HIREDATE","SAL","COMM","DEPTNO") values (7521
  ,'WARD','SALESMAN',7698,to_date('1981-02-22',
  'DD-MON-RR'),1250,500,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
  "HIREDATE","SAL","COMM","DEPTNO") values (7566
  ,'JONES','MANAGER',7839,to_date('1981-04-02',
  'DD-MON-RR'),2975,null,20);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
  "HIREDATE","SAL","COMM","DEPTNO") values (7654
  ,'MARTIN','SALESMAN',7698,to_date('1981-09-28',
  'DD-MON-RR'),1250,1400,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
  "HIREDATE","SAL","COMM","DEPTNO") values (7698
  ,'BLAKE','MANAGER',7839,to_date('1981-05-01',
  'DD-MON-RR'),2850,null,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
  "HIREDATE","SAL","COMM","DEPTNO") values (7782
  ,'CLARK','MANAGER',7839,to_date('1981-06-09',
  'DD-MON-RR'),2450,null,10);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
  "HIREDATE","SAL","COMM","DEPTNO") values (7788
  ,'SCOTT','ANALYST',7566,to_date('1987-04-19',
  'DD-MON-RR'),3000,null,20);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
  "HIREDATE","SAL","COMM","DEPTNO") values (7839
  ,'KING','PRESIDENT',null,to_date('1981-11-17',
  'DD-MON-RR'),5000,null,10);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
  "HIREDATE","SAL","COMM","DEPTNO") values (7844
  ,'TURNER','SALESMAN',7698,to_date('1981-09-08',
  'DD-MON-RR'),1500,0,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
  "HIREDATE","SAL","COMM","DEPTNO") values (7876
  ,'ADAMS','CLERK',7788,to_date('1987-05-23',
  'DD-MON-RR'),1100,null,20);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
  "HIREDATE","SAL","COMM","DEPTNO") values (7900
  ,'JAMES','CLERK',7698,to_date('1981-12-03',
  'DD-MON-RR'),950,null,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
  "HIREDATE","SAL","COMM","DEPTNO") values (7902
  ,'FORD','ANALYST',7566,to_date('1981-12-03',
  'DD-MON-RR'),3000,null,20);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
  "HIREDATE","SAL","COMM","DEPTNO") values (7934
  ,'MILLER','CLERK',7782,to_date('1982-01-23',
  'DD-MON-RR'),1300,null,10);

It is quite handy to post insert statements on forums, or move a table from one database to another just with copy paste, or export only a subset of the columns, because you can export a view too

TUNED_UNDORETENTION

How often I did meet ORA-01555: snapshot too old: rollback segment too small ?
I cannot count. On one of the database I am currently administrating it is about once a day.

Back to Oracle 7 and 8, the solution was usually to size the rollback segments properly. Using big rollback segments for big jobs, and many small segments for OLTP.

Oracle 9i introduced the automatic undo management. When you meet ORA-01555, just increase the UNDO tablespace (or/and set it autoextensible). Well, how did I read complains from users which used to have tiny rollback segments, and did not understand why the undo tablespace grows to gigabytes in 9i. There is also a parameter, called UNDO_RETENTION, which prevents Oracle from rewritting old extents before it really needs them.

In 10g, you can also force oracle to keep old undo extents up to the UNDO_RETENTION by using


alter tablespace UNDOTBS1 retention guarantee;

What I learned today is the TUNED_UNDORETENTION mechanism. By setting UNDO_RETENTION to 0, you enable auto tuned retention. That is, as long as your tablespace can autoextend, you will less probably get an ORA-01555. A recommended lecture is Metalink Note 240746.1

Back to my 9i database with ORA-01555, I will try to increase undo_retention to something bigger than


SQL> select max(maxquerylen) from v$undostat; 
 
MAX(MAXQUERYLEN)
----------------
          105047

And see how long I will survive until the next ORA-01555…

tablespace maintenance tasks

I just had to guide one of our DBA this morning.

How to get free of segments in SYSTEM tablespace :

  1. tables

    SQL> select owner, table_name from dba_tables where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    OWNER TABLE_NAME
    —– ———-
    SCOTT T

    SQL> alter table scott.t move tablespace users;

    Table altered.

    SQL> select owner, table_name from dba_tables where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);

    no rows selected

    If the table has a LONG datatype, than I need to exp/drop/create/imp the table


    SQL> select owner, table_name from dba_tables where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    OWNER TABLE_NAME
    —– ———-
    SCOTT T

    SQL> alter table scott.t move tablespace users;
    alter table scott.t move tablespace users
    *
    ERROR at line 1:
    ORA-00997: illegal use of LONG datatype

    SQL> ! exp file=t.dmp tables=t userid=scott/tiger

    Export: Release 10.2.0.2.0 – Production on Mon Aug 14 14:24:29 2006

    Copyright (c) 1982, 2005, Oracle. All rights reserved.

    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – Production
    With the Partitioning, OLAP and Data Mining options
    Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

    About to export specified tables via Conventional Path …
    . . exporting table T 3 rows exported
    Export terminated successfully without warnings.

    SQL> select dbms_metadata.get_ddl(‘TABLE’,’T’,’SCOTT’) from dual;
    DBMS_METADATA.GET_DDL(‘TABLE’,’T’,’SCOTT’)
    —————————————————————————————————————————————
    CREATE TABLE “SCOTT”.”T”
    ( “L” LONG
    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE “SYSTEM”

    SQL> drop table scott.t;

    Table dropped.

    SQL> CREATE TABLE “SCOTT”.”T”
    2 ( “L” LONG
    3 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
    4 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    5 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    6 TABLESPACE “USERS”;

    Table created.

    SQL> ! imp file=t.dmp tables=t userid=scott/tiger ignore=y

    Import: Release 10.2.0.2.0 – Production on Mon Aug 14 14:27:05 2006

    Copyright (c) 1982, 2005, Oracle. All rights reserved.

    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – Production
    With the Partitioning, OLAP and Data Mining options

    Export file created by EXPORT:V10.02.01 via conventional path
    import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
    . importing SCOTT’s objects into SCOTT
    . importing SCOTT’s objects into SCOTT
    . . importing table “T” 3 rows imported
    Import terminated successfully without warnings.

  2. Indexes

    SQL> select owner, index_name from dba_indexes where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    OWNER INDEX_NAME
    —– ———-
    SCOTT I

    SQL> alter index scott.i rebuild tablespace users;

    Index altered.

    SQL> select owner, index_name from dba_indexes where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);

    no rows selected

  3. lob
    warning: in 9i you must join with dba_tables to get tablespace_name

    SQL> select owner, table_name, column_name from dba_lobs where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    OWNER TABLE_NAME COLUMN_NAME
    —– ———- ———–
    SCOTT T          C

    SQL> alter table scott.t move lob(c) store as (tablespace users);

    Table altered.

    SQL> select owner, table_name, column_name from dba_lobs where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);

    no rows selected

  4. table partition

    SQL> select table_owner, table_name, partition_name from dba_tab_partitions where tablespace_name=’SYSTEM’ and table_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    TABLE_OWNER TABLE_NAME PARTITION_NAME
    ———– ———- ————–
    SCOTT       T          P1

    SQL> alter table scott.t move partition p1 tablespace users;

    Table altered.

    SQL> select table_owner, table_name, partition_name from dba_tab_partitions where tablespace_name=’SYSTEM’ and table_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);

    no rows selected

  5. index partition

    SQL> select index_owner, index_name, partition_name from dba_ind_partitions where tablespace_name=’SYSTEM’ and index_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    INDEX_OWNER INDEX_NAME PARTITION_NAME
    ———– ———- ————–
    SCOTT       I          SYS_P32

    SQL> alter index scott.i rebuild partition SYS_P32 tablespace users;

    Index altered.

    SQL> select index_owner, index_name, partition_name from dba_ind_partitions where tablespace_name=’SYSTEM’ and index_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);

    no rows selected

  6. table subpartition

    SQL> select table_owner, table_name, subpartition_name from dba_tab_subpartitions where tablespace_name=’SYSTEM’ and table_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    TABLE_OWNER TABLE_NAME SUBPARTITION_NAME
    ———– ———- ——————————
    SCOTT       T          SYS_SUBP30

    SQL> alter table scott.t move subpartition SYS_SUBP30 tablespace users;

    Table altered.

    SQL> select table_owner, table_name, subpartition_name from dba_tab_subpartitions where tablespace_name=’SYSTEM’ and table_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);

    no rows selected

  7. index subpartition

    SQL> select index_owner, index_name, subpartition_name from dba_ind_subpartitions where tablespace_name=’SYSTEM’ and index_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    INDEX_OWNER INDEX_NAME SUBPARTITION_NAME
    ———– ———- ——————————
    SCOTT       I          SYS_SUBP31

    SQL> alter index scott.i rebuild subpartition SYS_SUBP31 tablespace users;

    Index altered.

    SQL> select index_owner, index_name, subpartition_name from dba_ind_subpartitions where tablespace_name=’SYSTEM’ and index_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);

    no rows selected

  8. IOT

    SQL> select owner, table_name, index_name, index_type from dba_indexes where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    OWNER TABLE_NAME INDEX_NAME INDEX_TYPE
    —– ———- —————– ———-
    SCOTT T          SYS_IOT_TOP_10406 IOT – TOP

    SQL> alter table scott.t move tablespace users;

    Table altered.

    SQL> select owner, table_name, index_name, index_type from dba_indexes where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);

    no rows selected

  9. IOT Overflow

    SCOTTSQL> select owner, table_name, iot_name, iot_type from dba_tables where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    OWNER TABLE_NAME IOT_NAME IOT_TYPE
    —– —————— ——– ————
    SCOTT SYS_IOT_OVER_10406 T        IOT_OVERFLOW

    SQL> alter table scott.t move overflow tablespace users;

    Table altered.

    SQL> select owner, table_name, iot_name, iot_type from dba_tables where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);

    no rows selected

  10. cluster
    I did not find out yet :-( Probably not possible, so use exp+drop+create+imp as for long

  11. nested tables
    warning: according to the sql reference manual (not tested), in 9i, the nested table was moved with the parent table automatically

    SQL> select owner, table_name from dba_tables where nested=’YES’ and tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    OWNER TABLE_NAME
    —– ———-
    SCOTT CTT

    SQL> alter table scott.ctt move tablespace users;

    Table altered.

    SQL> select owner, table_name from dba_tables where nested=’YES’ and tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);

    no rows selected

  12. Lob partitions and subpartitions
    This is getting tricky… I did not achieve to change the default tablespace specification for lob, nor to change a HASH partition. Again, when all failed, use exp/drop/create/imp

    SQL> select owner, table_name, column_name from dba_lobs where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    OWNER TABLE_NAME COLUMN_NAME
    —– ———- —————–
    SCOTT T         C

    SQL> alter table scott.t move lob(c) store as (tablespace users);
    alter table scott.t move lob(c) store as (tablespace users)
    *
    ERROR at line 1:
    ORA-14511: cannot perform operation on a partitioned object

    SQL> select table_owner,table_name,column_name,partition_name from dba_lob_partitions where tablespace_name=’SYSTEM’ and table_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    TABLE_OWNER TABLE_NAME COLUMN_NAME PARTITION_NAME
    ———– ———- ———– ————–
    SCOTT       T         C           P1

    SQL> alter table scott.t move partition p1 lob(c) store as (tablespace users);

    Table altered.

    SQL> select table_owner,table_name,column_name,partition_name from dba_lob_partitions where tablespace_name=’SYSTEM’ and table_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);

    no rows selected

    SQL> select owner, table_name, column_name from dba_lobs where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    OWNER TABLE_NAME COLUMN_NAME
    —– ———- —————–
    SCOTT T          C

    the table was ranged partitioned. With a hash partition, i would get an ORA-22877

    SQL> alter table scott.t move partition sys_p47 lob(c) store as (tablespace users);
    alter table scott.t move partition sys_p47 lob(c) store as (tablespace users)
    *
    ERROR at line 1:
    ORA-22877: invalid option specified for a HASH partition or subpartition of a LOB column

The DBA also wanted to separate indexes and tables for tuning IOs. I had to redirect him to the doc :
Performance Tuning Guide
One popular approach to manual I/O distribution suggests separating a frequently used table from its index. This is not correct.
well, he shows me his Oracle 9i Tuning Course, Chapter 3, Slice 6, put table and indexes on different tablespaces… Ok, this is not a new thema, just google to find explanations !

sqlnet.wallet_override=true

I recently posted about Oracle Password Repository (OPR).

I did get a comment from Andreas Piesk about something similar in Oracle, the wallet.

Let’s do a quick test.

First, I create a .sqlnet.ora in my home directory (I do not want to mess up the system-wide sqlnet.ora).


$ cat /home/lsc/.sqlnet.ora
SQLNET.WALLET_OVERRIDE=TRUE
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/lsc)))

I now create the wallet


$ mkstore -create -wrl /home/lsc
Enter password:
Enter password again:

and the credentials


$ mkstore -wrl /home/lsc -createCredential LSC01 scott tiger
Enter password:
Create credential oracle.security.client.connect_string1

now I try to login


$ sqlplus /@LSC01

SQL*Plus: Release 10.2.0.2.0 - Production on Thu Aug 10 11:23:35 2006

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> sho user
USER is "SCOTT"

This sounds to work very nicely. Let’s see if this is more secure than OPR :


$ mkstore -wrl /home/lsc -list
Enter password:

Oracle Secret Store entries:
oracle.security.client.connect_string1
oracle.security.client.password1
oracle.security.client.username1
$ mkstore -wrl /home/lsc -viewEntry oracle.security.client.connect_string1
Enter password:
oracle.security.client.connect_string1 = LSC01
$ mkstore -wrl /home/lsc -viewEntry oracle.security.client.username1
Enter password:
oracle.security.client.username1 = scott
$ mkstore -wrl /home/lsc -viewEntry oracle.security.client.password1
Enter password:
oracle.security.client.password1 = tiger

Definitely! The password is not reveal, unless you know the password of the wallet. Remember in OPR, the application had direct access to the password. Here it is not the case, if you do not know the password of the wallet, you may login, but you cannot find out what the password is. I like this very much.

of course do not forget to protect your wallet

Ok, what I did not achieve until yet is logging in externally when using wallet_override :


$ mv /home/lsc/.sqlnet.ora /home/lsc/.sqlnet.ora.disable
$ sqlplus /

SQL*Plus: Release 10.2.0.2.0 - Production on Thu Aug 10 11:35:56 2006

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> sho user
USER is "OPS$LSC"
$ mv /home/lsc/.sqlnet.ora.disable /home/lsc/.sqlnet.ora
$ sqlplus /

SQL*Plus: Release 10.2.0.2.0 - Production on Thu Aug 10 11:37:13 2006

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

I will update this if I find out how to login externally too.

select last rows

I just read about a query to retrieve last modification row of a date


SQL> SELECT ora_rowscn FROM tab_test;

ORA_ROWSCN
----------
351744
351744
351744
351744
351744
351744
6 rows selected.

SQL> UPDATE tab_test SET valeur=valeur*1.1 WHERE col_id=1;

3 rows updated.

SQL> commit;

Commit complete

SQL>  SELECT ora_rowscn FROM tab_test:

ORA_ROWSCN
----------

351744
351744
351744
371423
371423
371423

6 rows selected.

conclusion of the author : very neat to retrieve last modification date.

Well, I am worried. I have answered so many times on the technical forums here for example, that the only way to retrieve the last rows is to use a date column, and to manually update it (or with a trigger or a default value for insert) with the last modification date.

What should I do?
test it!


14:52:07 SQL> create table t as 
select rownum x from all_objects;

Table created.

14:56:23 SQL> select scn_to_timestamp(ora_rowscn) timestamp, count(*) from t group by scn_to_timestamp(ora_rowscn);

TIMESTAMP                COUNT(*)
---------------------- ----------
04.08.2006 14:56:23.00       4238

let’s update one row


14:54:12 SQL> update t set x=-1 where rownum=1;

1 row updated.

14:55:04 SQL> commit;

Commit complete.

14:58:03 SQL> select scn_to_timestamp(ora_rowscn) timestamp, count(*) from t group by scn_to_timestamp(ora_rowscn) order by scn_to_timestamp(ora_rowscn);

TIMESTAMP                COUNT(*)
---------------------- ----------
04.08.2006 14:56:23.00       3580
04.08.2006 14:57:14.00        658

what? I updated one row, why did it updated so many rows? Let’s look at the block


14:58:16 SQL>  select dbms_rowid.rowid_block_number(rowid) block_number,scn_to_timestamp(ora_rowscn) timestamp,count(*) from t group by dbms_rowid.rowid_block_number(rowid),scn_to_timestamp(ora_rowscn) order by scn_to_timestamp(ora_rowscn);

BLOCK_NUMBER TIMESTAMP                COUNT(*)
------------ ---------------------- ----------
         651 04.08.2006 14:56:23.00        658
         652 04.08.2006 14:56:23.00        658
         653 04.08.2006 14:56:23.00        658
         654 04.08.2006 14:56:23.00        658
         655 04.08.2006 14:56:23.00        658
         656 04.08.2006 14:56:23.00        290
         650 04.08.2006 14:57:14.00        658 <=====================

ok, what has the doc to say about this :

For each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is useful for determining approximately when a row was last updated. It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides. You can obtain a more fine-grained approximation of the SCN by creating your tables with row-level dependency tracking

Well, let’s try again with row dependencies.


15:04:53 SQL> drop table t;

Table dropped.

15:04:55 SQL> create table t rowdependencies as select rownum x from all_objects;

Table created.

15:05:28 SQL> select scn_to_timestamp(ora_rowscn) timestamp, count(*) from t group by scn_to_timestamp(ora_rowscn) order by scn_to_timestamp(ora_rowscn);

TIMESTAMP                COUNT(*)
---------------------- ----------
04.08.2006 15:05:26.00       4241

15:05:42 SQL> update t set x=-1 where rownum=1; 
1 row updated.

15:05:58 SQL> commit;

Commit complete.

15:06:09 SQL> select scn_to_timestamp(ora_rowscn) timestamp, count(*) from t group by scn_to_timestamp(ora_rowscn) order by scn_to_timestamp(ora_rowscn);

TIMESTAMP                COUNT(*)
---------------------- ----------
04.08.2006 15:05:26.00       4240
04.08.2006 15:06:08.00          1

sounds better! probably not 100% reliable but way better, it seems almost usable …

Oracle Password Repository

I checked this tool today :
http://sourceforge.net/projects/opr

This tool provide a simple way of not hardcoding passwords in shell scripts. Hardcoding passwords in shell scripts is a bad practice. The source code may be shared by many developers, may resides on unsecure servers (CVS), may be printed, etc… The passwords may change often too.

This tool uses a simple file to stores the passwords encrypted. Well, I urge you to secure this file to make it non-accessible for others.

I am not going to decode nor rate the encryption algorythm, the fact is, you do not have clear text passwords. It could be also possible to have clear text password in a separate text file, which would not be that less secure, but it is ugly.

To make it clear (I hope), it is a 2-ways encryption, so if you have access to the file and the source code of the algorythm, you can crack the password. Sounds weak? Well, Oracle Proxy Users with Internet Directory, Application Server, Portal and all those products are not much different. The only 100% passwordless solution I am aware of is the external OS identification.

Well, that said, let’s look how it works.

# ./configure
# make
# make install

let’s create the repository

 
$ export OPRREPOS=$ORACLE_HOME/dbs/oprrepos
$ opr -c

store the password for scott on LSC01

$ opr -a LSC01 SCOTT lsc
please enter the password :
please re-enter the password :
entry (LSC01, scott, lsc) added.

let’s test

$ sqlplus scott/$(opr -r LSC01 SCOTT)@LSC01

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Jul 25 13:51:48 2006

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

This is quite convenient. I will use this to store the RMAN password to connect to the RMAN repository in my backup scripts

exp/imp

EXP / IMP has a quite a lot of bugs and limitations.

Today I had a trouble with a function based index first, generating ORA-942 table does not exist on import. I also had an error with AQ.

I have a few invalid objects too :

before exp, on source system (tru64/9.2.0.4)

SQL&gt; select count(*) from dba_objects where status='INVALID';
 
  COUNT(*)
----------
        54

after imp, on target system (rh4/10.2.0.2)

SQL&gt; select count(*) from dba_objects where status='INVALID';
 
  COUNT(*)
----------
    142240

let’s recompile

SQL&gt; @?/rdbms/admin/utlrp

SQL&gt; select count(*) from dba_objects where status='INVALID';
 
  COUNT(*)
----------
      2699

… better, but still too much !

I probably did hit bug 3169196 too. The workaround is delicious :

<b>manually clean the data dictionary</b>

Well, I am going to drop the destination database and try again…

set my ORACLE_HOME, PATH, ORACLE_SID

I posted yesterday some of my aliases. My favorite (and most obfuscated) one is the following (for bash)


eval $(awk -F: '/^[+a-zA-Z]/{l=tolower($1); sub("^+","",l); print "alias "l"=47x="$2";PATH=${PATH//$ORACLE_HOME/$x}; ORACLE_HOME=$x; ORACLE_SID="$1"; echo ORACLE_SID="$1"; 47; "}' /etc/oratab 2&gt;/dev/null)

I am setting my path and a default sid/home in my .profile


[ -z "$ORACLE_SID" ] &amp;&amp; export ORACLE_SID=LSC01
export ORACLE_HOME=$(sed -n "s/:.$//;s/^$ORACLE_SID://p" /etc/oratab)
PATH=$ORACLE_HOME/bin
PATH=$PATH:$ORACLE_HOME/opmn/bin
PATH=$PATH:$ORACLE_HOME/dcm/bin
PATH=$PATH:$HOME/bin
PATH=$PATH:/usr/local/bin
PATH=$PATH:/usr/bin
PATH=$PATH:/usr/X11R6/bin
PATH=$PATH:/bin
PATH=$PATH:/usr/sbin
PATH=$PATH:/opt/gnome/bin
PATH=$PATH:/opt/kde3/bin
PATH=$PATH:.
export PATH

Than in my eval command above set an alias for each line in /etc/oratab

# /etc/oratab
LSC01:/app/oracle/oracle/product/10.2.0/db_2:Y
+ASM1:/home/oracle/oracle/product/10.2.0/db_2:Y
RAC1:/home/oracle/oracle/product/10.2.0/db_2:Y
RAC2:/home/oracle/oracle/product/10.2.0/db_2:Y
crs:/app/oracle/product/10.2.0/crs:N

so here is the list of my dynamically generated aliases

alias asm1='x=/home/oracle/oracle/product/10.2.0/db_2; PATH=${PATH//$ORACLE_HOME/$x}; ORACLE_HOME=$x; ORACLE_SID=+ASM1; echo ORACLE_SID=+ASM1;'
alias crs='x=/app/oracle/product/10.2.0/crs; PATH=${PATH//$ORACLE_HOME/$x};ORACLE_HOME=$x; ORACLE_SID=crs; echo ORACLE_SID=crs;'
alias lsc01='x=/app/oracle/oracle/product/10.2.0/db_2; PATH=${PATH//$ORACLE_HOME/$x};ORACLE_HOME=$x; ORACLE_SID=LSC01; echo ORACLE_SID=LSC01;'
alias rac1='x=/home/oracle/oracle/product/10.2.0/db_2; PATH=${PATH//$ORACLE_HOME/$x}; ORACLE_HOME=$x; ORACLE_SID=RAC1; echo ORACLE_SID=RAC1;'
alias rac2='x=/home/oracle/oracle/product/10.2.0/db_2; PATH=${PATH//$ORACLE_HOME/$x}; ORACLE_HOME=$x; ORACLE_SID=RAC2;echo ORACLE_SID=RAC2;'

One alias I also like to set is a switch-user alias + keep settings and profile !
alias oracle='su - oracle -c "DISPLAY=$DISPLAY ORACLE_HOME=$ORACLE_HOME ORACLE_SID=$ORACLE_SID PATH=$PATH bash --rcfile ~lsc/.bashrc"'

I also have exotic, less recommendable, aliases…


alias +='sqlplus -L /'
alias -- -='cd -'
alias ..='cd ..'
alias ...='cd ../..'
alias ....='cd ../../..'
alias .....='cd ../../../..'
alias ......='cd ../../../../..'
alias .......='cd ../../../../../..'
alias ........='cd ../../../../../../..'

I also have a very handy function, called p

p() {
    sqlplus -L -s "/ as sysdba" &lt;&lt;EOF  | sed -n 's/@ //p'
set echo off lin 9999 trimsp on feedb off head off pages 0 tab off
col name for a25
select '@',name, value from v\$parameter2 where upper(name) like upper('%$1%');
EOF
}
P() {
    sqlplus -L -s "/ as sysdba" &lt;&lt;EOF  | sed -n 's/@ //p'
set echo off lin 9999 trimsp on feedb off head off pages 0 tab off
col name for a25
select '@',ksppinm name,ksppstvl value FROM x\$ksppi join x\$ksppcv using (inst_id,indx) where upper(ksppinm) like upper('%$1%');
EOF
}

so I can check the parameter directly from the shell

$ P shared_pool_size
_io_shared_pool_size      4194304
shared_pool_size          0
__shared_pool_size        83886080

I also use KSH sometimes, there I have a slicly different version of my aliases…

dba workshop

Last week I gave an internal 2-days dba course. All three students had good experience of filesystem backup and unix, but hardly any oracle experience.

I figured out recovery is more complex than I thought! By explaining to other, how often do you realise do you do not know the answer yourself? It has been very educative to me too…

First inconsistency I discovered :
I tried to simulate a disaster by removing all controlfiles, redologs, datafiles. I expected the students to notice “something”. But, O Surprise, the db kept running, nothing written in alert logs, it was possible to create and drop tables, nothing went wrong (but they were no files). Probably the OS has not notified the file were no more there, and Oracle opened the files already. Strange. Even SHUTDOWN has been successful! But of course STARTUP did not work.

Next time I prepare a B&R workshop, I must invest time to check that a disaster is effective!

Second inconsistency:

Report unrecoverable datafiles and validate backups. I wrote this in the rman technology forum this morning: REPORT NEED BACKUP is not suffisant to have consistent backups, nor RESTORE VALIDATE DATABASE!

More inconsistencies :
somehow, when users are doing backups with nocatalog and try to restore with the recovery manager catalog, it is not working. Well, it is not that surprising, but it really increases the difficulty of recovery procedures!

However, those “inconsitencies” were good in this informal training, feedback from students was positive, we did “survive” all those problems and they realised RMAN recovery is not as simple as RMAN backup…

to_char(interval)

There is no to_char function available for intervals.

Or at least it does not work as expected


SQL&gt; select to_char(interval '1234' second, 'HH24:MM') from dual;
TO_CHAR(INTERVAL'12
-------------------
+00 00:20:34.000000

I just write my own one, with some new format elements

For interval day to second, I have DDD number of days, HH number of hours (0-24), HHH total number of hours (0-99999999999999), etc

here it is


SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 9 17:15:59 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL&gt; 
SQL&gt; create or replace function tochards(f_int interval day to second,f_fmt varchar2) return varchar2 is
  2  -- valid formats are DDD, HHH, HH, MMM, MM, SSS, SS, FF
  3      ret varchar2(4000);
  4      f varchar2(4000);
  5      i interval day(9) to second(9);
  6  begin
  7      if (f_fmt is null or f_int is null) then
  8          return null;
  9      end if;
 10      f := upper(f_fmt);
 11      if (translate(f,'XDHMSF,.:;/- ','X') is not null) then
 12          raise_application_error(-20001,'Invalid format');
 13      end if;
 14      if (extract(day  from i)&lt;0) then
 15          ret:='-';
 16          i:=f_int*(-1);
 17      else
 18          ret:='';
 19          i:=f_int;
 20      end if;
 21      while (f is not null) loop
 22          if (f like 'DDD%') then
 23              ret:=ret||to_char(extract(day from i),'FM999999999999999999');
 24              f:=substr(f,4);
 25          elsif (f like 'HHH%') then
 26              ret:=ret||to_char(extract(day from i)*24+extract(hour from i),'FM999999999999999999');
 27              f:=substr(f,4);
 28          elsif (f like 'HH%') then
 29              ret:=ret||to_char(extract(hour from i),'FM999999999999999999');
 30              f:=substr(f,3);
 31          elsif (f like 'MMM%') then
 32              ret:=ret||to_char(extract(day from i)*24*60+extract(hour from i)*60+extract(minute from i),'FM999999999999999999');
 33              f:=substr(f,4);
 34          elsif (f like 'MM%') then
 35              ret:=ret||to_char(extract(minute from i),'FM999999999999999999');
 36              f:=substr(f,3);
 37          elsif (f like 'SSS%') then
 38              ret:=ret||to_char(extract(day from i)*24*60*60+extract(hour from i)*60*60+extract(minute from i)*60+trunc(extract(second from i)),'FM999999999999999999');
 39              f:=substr(f,4);
 40          elsif (f like 'SS%') then
 41              ret:=ret||to_char(trunc(extract(second from i)),'FM999999999999999999');
 42              f:=substr(f,3);
 43          elsif (f like 'FF%') then
 44              ret:=ret||to_char(mod(extract(second from i),1),'FM999999999999999999');
 45              f:=substr(f,3);
 46          elsif (substr(f,1,1) in (' ', '-', ':', ';', ',', '.', '/')) then
 47              ret:=ret||substr(f,1,1);
 48              f:=substr(f,2);
 49          else
 50              raise_application_error(-20001,'Invalid format : '||f_fmt);
 51          end if;
 52      end loop;
 53      return ret;
 54  end;
 55  /

Function created.

SQL&gt; 
SQL&gt; sho err
No errors.
SQL&gt; 
SQL&gt; create or replace function tocharym(f_int interval year to month,f_fmt varchar2) return varchar2 is
  2  -- valid formats are YYY, MMM, MM
  3      ret varchar2(4000);
  4      f varchar2(4000);
  5      i interval year to month;
  6  begin
  7      if (f_fmt is null or f_int is null) then
  8          return null;
  9      end if;
 10      f := upper(f_fmt);
 11      if (translate(f,'XYM,.:;/- ','X') is not null) then
 12          raise_application_error(-20001,'Invalid format');
 13      end if;
 14      if (extract(year  from i)&lt;0) then
 15          ret:='-';
 16          i:=f_int*(-1);
 17      else
 18          ret:='';
 19          i:=f_int;
 20      end if;
 21      while (f is not null) loop
 22          if (f like 'YYY%') then
 23              ret:=ret||to_char(extract(year from i),'FM999999999999999999');
 24              f:=substr(f,4);
 25          elsif (f like 'MMM%') then
 26              ret:=ret||to_char(extract(year from i)*12+extract(month from i),'FM999999999999999999');
 27              f:=substr(f,4);
 28          elsif (f like 'MM%') then
 29              ret:=ret||to_char(extract(month from i),'FM999999999999999999');
 30              f:=substr(f,3);
 31          elsif (substr(f,1,1) in (' ', '-', ':', ';', ',', '.', '/')) then
 32              ret:=ret||substr(f,1,1);
 33              f:=substr(f,2);
 34          else
 35              raise_application_error(-20001,'Invalid format : '||f_fmt);
 36          end if;
 37      end loop;
 38      return ret;
 39  end;
 40  /

Function created.

SQL&gt; 
SQL&gt; sho err
No errors.
SQL&gt; 
SQL&gt; select tochards(current_timestamp - timestamp '2000-01-01 00:00:00', 'HHH:MM') from dual;

TOCHARDS(CURRENT_TIMESTAMP-TIM
------------------------------
53561:15

SQL&gt; select tochards(current_timestamp - timestamp '2000-01-01 00:00:00', 'DDD HH:MM:SS') from dual;

TOCHARDS(CURRENT_TIMESTAMP-TIM
------------------------------
2231 17:15:59

SQL&gt; select tocharym(interval '25' month, 'YYY:MM') from dual;

TOCHARYM(INTERVAL'25'MONTH,'YY
------------------------------
2:1

SQL&gt; select tocharym(interval '-25' month, 'MMM') from dual;

TOCHARYM(INTERVAL'-25'MONTH,'M
------------------------------
-25

SQL&gt; Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

difference between EXPLAIN PLAN and SET AUTOTRACE TRACEONLY EXPLAIN

most of the time I use set autot trace exp in order to get the execution plan. It seems more easy than explain plan for [query]; followed by select * from table (dbms_xplan.display);.

However, take care, set autotrace traceonly explain does modify the rows if you explain a plan for insert/update/delete.

SQL&gt; set autot trace exp
SQL&gt; delete emp;

14 rows deleted.

Execution Plan
----------------------------------------------------------
Plan hash value: 3538878155

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |      |    14 |    98 |     2   (0)| 00:00:01 |
|   1 |  DELETE            | EMP  |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |    98 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

SQL&gt; set autot off
SQL&gt; select count(*) from emp;
         0
SQL&gt; roll
Rollback complete.

but explain plan does not

SQL&gt; select count(*) from emp;
        14

SQL&gt; explain plan for delete emp;

Explained.

SQL&gt; select * from table (dbms_xplan.display);  
Plan hash value: 3538878155

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |      |    14 |    98 |     2   (0)| 00:00:01 |
|   1 |  DELETE            | EMP  |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |    98 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

SQL&gt; select count(*) from emp;
        14

change MAXDATAFILES, MALOGFILES, MAXINSTANCES, MAXLOGMEMBERS without downtime

this is a 10gR2 new feature. It is no longer necessary to recreate the control file to increase those parameters. Actually, you do not have to change them, they change “automatically”

Demo


SQL> CREATE DATABASE
MAXDATAFILES 5
MAXINSTANCES 1
MAXLOGFILES 2
MAXLOGMEMBERS 1
extent management local
default tablespace users
default temporary tablespace temp
undo tablespace undotbs1;

Database created.

SQL> create tablespace t1;

Tablespace created.

SQL> create tablespace t2;

Tablespace created.

SQL> select count(*) from v$datafile;
  COUNT(*)
----------
         6

datafiles exceeded, but no error!


SQL> alter database add logfile 
  ('/dbms/oracle/LSC75/redo/f1.sql',
  '/dbms/oracle/LSC75/redo/f2.sql')  size 16M;

Database altered.

SQL> select group#, members from v$log;
    GROUP#    MEMBERS
---------- ----------
         1          1
         2          1
         3          2

logfiles and logmembers exceeded, but no error!


SQL> alter database add logfile instance 'I2';   

Database altered.

SQL> alter database add logfile instance 'I2';   

Database altered.

SQL> alter database enable instance 'I2';

Database altered.

SQL> select count(*) from v$INSTANCE_LOG_GROUP;
  COUNT(*)
----------
         2

instances exceeded, but no error!

so well, then why bother any more about specifying a big MAXDATAFILES and MAXLOGFILES at db creation?

select * from test where my_long like ‘%toto%’

A good way to learn is to try to answer user questions. Instead of referencing other posts, I tried today to answer that frequently asked question myself on developpez.com (french forum)


SQL> create table test ( my_long long);

Table created.

SQL> insert into test values ('hello toto !');

1 row created.

SQL> exec for r in ( select my_long from test ) 
   loop if (r.my_long like '%toto%') then 
   dbms_output.put_line(r.my_long); end if; 
   end loop 
hello toto !

lock system, restrict dbsnmp

An unlocked user is a security problem. Currently, all my unlocked users have only CREATE SESSION as system privilege, evtl ALTER SESSION.

Except SYS, SYSTEM and DBSNMP

To minimize this security problem, I implemented the following strategy on my test system.

1) delete password file, set remote_login_passwordfile=NONE, O7_DICTIONARY_ACCESSIBILITY=FALSE
2) alter user SYSTEM account lock;
3a) in 10gR2 :
alter user dbsnmp quota 1T on sysaux;
create role secure_oem_role;
grant advisor, analyze any, analyze any dictionary, create job, create procedure, create session, create table, manage any queue, select any dictionary to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_AQ” to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_AQADM” to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_DRS” to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_MONITOR” to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_SERVER_ALERT” to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_SYSTEM” to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_WORKLOAD_REPOSITORY” to secure_oem_role;
exec SYS.DBMS_AQADM.GRANT_QUEUE_PRIVILEGE(‘DEQUEUE’, ‘ALERT_QUE’, ‘SECURE_OEM_ROLE’)
revoke EXECUTE ON “SYS”.”DBMS_SERVER_ALERT” from dbsnmp;
revoke EXECUTE ON “SYS”.”DBMS_SYSTEM” from dbsnmp;
revoke UNLIMITED TABLESPACE from dbsnmp;
revoke SELECT ANY DICTIONARY from dbsnmp;
revoke CREATE PROCEDURE from dbsnmp;
revoke CREATE TABLE from dbsnmp;
revoke OEM_MONITOR from dbsnmp;
grant secure_oem_role to dbsnmp;
3b) in other versions, you probably can remove more and grant less, I think only in 10g it is necessary to have “quota”. In my other databases, dbsnmp have 0 segments.

Check what system privileges are potentially dangerous to the system :

select path
from
(
select
grantee,
sys_connect_by_path(privilege, ‘:’)||’:’||grantee path
from (select grantee, privilege, 0 role from dba_sys_privs union all select grantee, granted_role, 1 role from dba_role_privs)
connect by privilege=prior grantee
start with role=0
)
where
grantee in (
select username from dba_users
where lock_date is null
and password != ‘EXTERNAL’
and username != ‘SYS’)
or grantee=’PUBLIC’
/
:ADVISOR:SECURE_OEM_ROLE:DBSNMP
:ANALYZE ANY:SECURE_OEM_ROLE:DBSNMP
:ANALYZE ANY DICTIONARY:SECURE_OEM_ROLE:DBSNMP
:CREATE JOB:SECURE_OEM_ROLE:DBSNMP
:CREATE PROCEDURE:SECURE_OEM_ROLE:DBSNMP
:CREATE SESSION:USER1
:CREATE SESSION:USER2
:CREATE SESSION:SECURE_OEM_ROLE:DBSNMP
:CREATE TABLE:SECURE_OEM_ROLE:DBSNMP
:MANAGE ANY QUEUE:SECURE_OEM_ROLE:DBSNMP
:SELECT ANY DICTIONARY:SECURE_OEM_ROLE:DBSNMP

it sounds better…

idle events in 10gR2

I just noticed this morning that idle events are very easily identifiable by a new column called wait_class in 10gR2

To ignore idle event, I just wrote


select WAIT_CLASS, event
from (
  select * 
  from V$SYSTEM_EVENT 
  where WAIT_CLASS#!=6
  order by TIME_WAITED_MICRO desc)
where rownum<6 ;

WAIT_CLASS    EVENT
------------- ----------------------------------------
System I/O    log file parallel write                  
Configuration log file switch (checkpoint incomplete)
Configuration log file switch completion             
System I/O    db file parallel write                 
System I/O    control file parallel write            

sys_connect_by_path in 8i or the danger to use undocumented parameters…

I have been posting on metalink technical forum about a query that I run against all my test databases but did not work in production.

as it simpliest form

select sys_connect_by_path(dummy,’:’) from dual connect by 1=2;

well, there is nothing wrong with this query. I tried it on 8i, 9iR2, 10gR1 10gR2 and it worked fine. In production, it just refused to work on 8i.

Well, I have an other bitset of 8i on my test environment, and an other operating system version. So I supposed it must be related to the old production 64bits AIX4 os.

After reading the docs again, I discovered that SYS_CONNECT_BY_PATH is not documented in 8i. So why did this work on my test system then? Because I have _new_connect_by_enabled = true in my init.ora.

Ok, on the one hand, I found out an (unsupported) way to let my query run in 8i

alter session set “_new_connect_by_enabled”=TRUE;

On the other hand, I have been tricked by my own parameter file, setting one hidden parameter ages ago, forgetting about it, and the query “unhopefully succeeded” in my test environment… revealing the bug only once distributed !

encrypted listener password

There a few major changes in the database administration and the database security between 9i and 10g.

In 9i, I used to grep in the listener.ora to find out the password.

LISTENER_LSC61 =
(DESCRIPTION=(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10061)(QUEUESIZE=200))
))
PASSWORDS_LISTENER_LSC61 = 1234567890ABCDEF

this 64bit encrypted string can be used in 9i to stop the listener

$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 9.2.0.6.0 – Production on 05-DEC-2005 14:33:51

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Welcome to LSNRCTL, type “help” for information.

LSNRCTL> set current_listener listener_lsc61
Current Listener is listener_lsc61
LSNRCTL> set password 1234567890ABCDEF
The command completed successfully
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10061)(QUEUESIZE=200)))
The command completed successfully

As a dba, it is quite handy, because you can use grep (or awk) to find out the password out of the listener.ora. As a security admin, you should make sure the listener.ora is not readable. Note that the default, when created by netmgr, is to be world-readable :-(

However, this does no longer work in 10g

LISTENER_LSC62 =
(DESCRIPTION=(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10062)(QUEUESIZE=200))
))

PASSWORDS_listener_LSC62 = 1234567890ABCDEF

the encrypted string can no longer be used

$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 10.1.0.4.0 – Production on 05-DEC-2005 14:37:24

Copyright (c) 1991, 2004, Oracle. All rights reserved.

Welcome to LSNRCTL, type “help” for information.

LSNRCTL> set current_listener listener_lsc62
Current Listener is listener_lsc62
LSNRCTL> set password 1234567890ABCDEF
The command completed successfully
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10062)(QUEUESIZE=200)))
TNS-01169: The listener has not recognized the password
TNS-01189: The listener could not authenticate the user

As a security admin, you would think it is better so. But, how are you going to stop the listener in your script? Well, in 10g, we can use local authentification (default). So if the script is started as oracle, we would not need to use password

LISTENER_LSC63 =
(DESCRIPTION=(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10016)(QUEUESIZE=200))
))

PASSWORDS_listener_LSC63 = 1234567890ABCDEF

$ whoami
oracle
$ hostname
dbsrv85a.ex.zkb.ch
$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.1.0 – Production on 05-DEC-2005 14:43:33

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Welcome to LSNRCTL, type “help” for information.

LSNRCTL> set current_listener LISTENER_LSC63
Current Listener is LISTENER_LSC63
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10016)(QUEUESIZE=200)))
The command completed successfully

I read in an Alex Kornbrust post on Pete Finnigan forum, that a LOCAL_OS_AUTHENTICATION “undocumented” parameter could be used to “avoid” local authentication, but in that case, it is going to be a nightmare to “stop” the listener in an automated script, well, we can still use “kill”, but it is not very beautifoul.

post from palindnilap

I just answered a question about the data dictionary on forums.oracle.com. It is all about the dictionary views.
How many tables should I know in the dictionary ?

SQL> select count(*) from dict;
1857

Well, that’s too much. But I can remove the GV$ view, which contain the instance for RAC, and the DBA_ ALL_ and USER_ have (almost) the same structure.

SQL> select count(*)
from dict
where table_name not like ‘GV$%’
and table_name not like ‘ALL%’
and table_name not like ‘DBA%’ ;
712

Anyway, who knows the 712 views by heart? Hopefully, there is one called DICTIONARY, which helps !

Coming back to the post, palindnilap wants to see which columns of a view are mapped to which column of a table. A quick look at ALL_VIEWS could do the trick, but than you will need to “understand” the query to see which view.column maps to which table.column. What’s more, ALL_VIEWS.TEXT is a long. Arghh!

if you have a view that contains all columns from a table, you could use ALL_DEPENDENCIES to see on which table it is based.

On my first answer, I pointed out that ALL_UPDATABLE_COLUMNS may reveal that a view column belongs to a table if the column is updatable.

My last try was to use the ACCESS_PREDICATES to get the column physically accessed.

SQL> select * from v02 where employee=123456;

no rows selected

SQL> select
max(substr(ACCESS_PREDICATES,1,instr(ACCESS_PREDICATES,’=’)-1))
from v$sql_plan
where ACCESS_PREDICATES like ‘%=123456′;
“EMPNO”

here we see EMPLOYEE is actually named “EMPNO” in the based table. It could be done with explain plan and PLAN_TABLE too.

oracle voyage worm

I wrote a mini script to protect my customer from being attacked by an “oracle voyage worm” variant :

revoke CREATE DATABASE LINK from CONNECT;
revoke ALL on SYS.UTL_FILE from PUBLIC;
revoke ALL on SYS.UTL_HTTP from PUBLIC;
revoke ALL on SYS.UTL_SMTP from PUBLIC;
revoke ALL on SYS.UTL_TCP from PUBLIC;
grant EXECUTE on SYS.UTL_FILE to XDB;
grant EXECUTE on SYS.UTL_HTTP to MDSYS;
grant EXECUTE on SYS.UTL_HTTP to ORDPLUGINS;
@?/rdbms/admin/utlrp


Than, in OEM 10g, check for policy violations.

I added a few grants to special oracle internal users, to avoid invalid objects, which is also a policy violation in OEM… OEM will report a violation if those accounts are not locked and expired

10.2.0.1 hidden parameters

In order to get a clean database configuration, I add the following two hidden parameters in my 10.2.0.1 parameter file.
Do not hurl that loud, I hear you from here!

Well, as I said already about the _pga_max_size, I never recommend using hidden parameters when you can do the same without.

However, I am going to communicate those parameters, and the metalink notes referencing them.
You will need them to have a cleaner 10.2.0.1 installation

  • _kgl_large_heap_warning_threshold=33554432
    This parameter prevent Heap size 2800K exceeds notification threshold errors in the alert log and user trace dumps.
    Note: 330239.1 Bugs: 4286095, 4390265

  • __dg_broker_service_names=”
    In case you do not use dataguard but you do use local_listener parameter.
    this parameter prevents pmon from registering a <DB_NAME>_XPT.<DOMAIN_NAME> service in the listener.
    Thread: 611575.993 Bug: 4632635

    Probably all this will be fixed in 10.2.0.2

  • restore to a new host : nid++

    Great challenge today: restore to a new host from a closed noarchivelog backup on tape library.

    In oracle 8i and before, the only way to rename a database was to recreate the controlfile. In 9i, I could change it with nid, in 10gR2, I should never have a reason again to recreate the controlfile, because even the MAXDATAFILES and MAXINSTANCES could be changed. Change controlfile is bad. Once a collegue forget one file, once he noticed it, only months later, he wondered how to recover it. Create a controlfile is way to much sensible thing too do. It is almost as bad as changing the dictionary!

    Well. How to do than?

    Ok, I have a database called LSC67 on prod, I want to recover it to LSC66 in devl. I have NO ACCESS to production, but I have access to tapes (one may wonder if this is a good security practice…)

    Let’s start.

    First, if LSC66 already exists, shutdown abort. Remove datafiles. Remove controlfiles. Remove redo logs.

    Now I restore the data/control/redo files from prod:/dbms/oracle/LSC67 to devl in /dbms/oracle/LSC66.

    First I rename the files, some are called systemLSC67.dbf. I do not want that…

    find do the trick

    find /dbms/oracle/LSC66 -name “*LSC67*” |
    nawk ‘{printf “mv “$1″ “; gsub(src,target);print}’ src=LSC67 target=LSC66 |
    sh

    I must just change the DB_NAME in my parameter file (which already exists at my site), to reflect the prod controlfile dbname

    startup quiet force nomount
    alter system set db_name=’LSC66′ scope=spfile;
    startup quiet force mount

    now I generate some statements for dynamically renaming the files

    set ver off emb on pages 0 newp 0 lin 9999 trims on head off feedb off termout off
    spool /tmp/rename_LSC67_to_LSC66.sql
    select ‘alter database rename file ”’||name||”’ to ”’||replace(name,’LSC67′,’LSC66′)||”’;’
    from (
    select name from v$datafile
    union all
    select member from v$logfile
    )
    where name like ‘%LSC67%’;
    spool off
    spool /tmp/drop_temp_LSC67_to_LSC66.sql
    select ‘alter database tempfile ”’||tf.name||”’ drop;’
    from v$tempfile tf
    where tf.name like ‘%LSC67%’;
    spool off
    spool /tmp/create_temp_LSC67_to_LSC66.sql
    select ‘alter tablespace “‘||ts.name||’” add tempfile ”’||
    replace(tf.name,’LSC67′,’LSC66′)||
    ”’ size 128M reuse autoextend on next 128M maxsize 2048M;’
    from v$tablespace ts , v$tempfile tf
    where tf.name like ‘%LSC67%’ and tf.ts#=ts.ts#;
    spool off

    ok, now I am in mount, I do a rename datafile and drop tempfile. after I open database, and add tempfile. I am not taking care of the original size and autoextend clause of the original tempfiles, just 128M next 128M max 2G.

    set echo on termout on feedb 6
    @/tmp/rename_LSC67_to_LSC66.sql
    @/tmp/drop_temp_LSC67_to_LSC66.sql
    alter database open;
    @/tmp/create_temp_LSC67_to_LSC66.sql

    now I nid

    shutdown immediate
    startup quiet mount restrict

    nid dbname=LSC66 target=/

    and I change the db name and open resetlogs

    startup quiet force nomount
    alter system set db_name=’LSC66′ scope=spfile;
    startup quiet force mount
    alter database open resetlogs;

    FAILED_LOGIN_ATTEMPTS part 2

    Ref: part 1
    I reported this lack of documentation on http://forums.oracle.com/forums/thread.jspa?threadID=330359

    Here is my test case (take care, it will create a new db!) :

    SQL&gt; startup force quiet nomount;
    ORACLE instance started.
    SQL&gt; create database controlfile reuse extent management
    local default tablespace users default temporary tablespace temp
    undo tablespace undotbs1;
    
    Database created.
    SQL&gt; @?/rdbms/admin/catalog
    SQL&gt; @?/rdbms/admin/catproc
    SQL&gt; col username for a10
    SQL&gt; col PROFILE for a7
    SQL&gt; col LIMIT for a12
    SQL&gt; select username, profile, limit from dba_users join 
    dba_profiles using (profile) 
    where resource_name='FAILED_LOGIN_ATTEMPTS';
    USERNAME   PROFILE LIMIT 
    ---------- ------- ------------ 
    SYSTEM     DEFAULT 10
    SYS        DEFAULT 10
    TSMSYS     DEFAULT 10
    DIP        DEFAULT 10
    DBSNMP     DEFAULT 10
    OUTLN      DEFAULT 10