Monday, November 30, 2009

Cannot export empty tables in 11.2

In 11g there is no segment allocated to the table until you insert, so the EXP utility fails to export empty tables.

[oracle@dev-oranode-221 ~]$ sqlplus a/a

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 30 14:38:06 2009

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining, Oracle Database Vault
and Real Application Testing options
SQL> create table imp_test(id integer primary key);

Table created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining, Oracle Database Vault
and Real Application Testing options
[oracle@dev-oranode-221 ~]$ exp a/a

Export: Release 11.2.0.1.0 - Production on Mon Nov 30 14:37:17 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining, Oracle Database Vault
and Real Application Testing options
Enter array fetch buffer size: 4096 >

Export file: expdat.dmp >

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 3

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > imp_test

EXP-00011: A.IMP_TEST does not exist
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >

Export terminated successfully with warnings.
[oracle@dev-oranode-221 ~]$ sqlplus a/a

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 30 14:38:06 2009

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining, Oracle Database Vault
and Real Application Testing options

SQL> insert into imp_test values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining, Oracle Database Vault
and Real Application Testing options
[oracle@dev-oranode-221 ~]$ exp a/a

Export: Release 11.2.0.1.0 - Production on Mon Nov 30 14:38:32 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining, Oracle Database Vault
and Real Application Testing options
Enter array fetch buffer size: 4096 >

Export file: expdat.dmp >

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 3

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > imp_test

. . exporting table IMP_TEST 1 rows exported
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >

Friday, November 27, 2009

ORA-32016: parameter "db_name" cannot be updated in SPFILE

Strange ... database won't let you change the db_name in the spfile when the database is mounted.

SYS AS SYSDBA@tl01bko1> alter system set db_name='t01bko' scope=spfile;

System altered.

SYS AS SYSDBA@tl01bko1> alter database mount;

Database altered.

SYS AS SYSDBA@tl01bko1> alter system set db_name='t01bko' scope=spfile;
alter system set db_name='t01bko' scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-32016: parameter "db_name" cannot be updated in SPFILE

Friday, November 20, 2009

Global Temporary Tables generate REDO and UNDO

Many people believe that Global Temporary Tables don't generate REDO or UNDO.
They do generate REDO and UNDO, but not as much as permanent tables.

# First a GTT :-

SQL> create global temporary table source as select * from dba_source where rownum <>

Table created.

SQL> set autotrace on statistics
SQL> insert into source select * from dba_source where rownum <>

99 rows created.


Statistics
----------------------------------------------------------
1 recursive calls
8 db block gets
771 consistent gets
0 physical reads
564 redo size
830 bytes sent via SQL*Net to client
818 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
99 rows processed


# And now a permanent table.

SQL> drop table source;

Table dropped.

SQL>
SQL> create table source as select * from dba_source where rownum <>


Table created.

SQL> SQL> set autotrace on statistics;
SQL> insert into source select * from dba_source where rownum <>

99 rows created.


Statistics
----------------------------------------------------------
721 recursive calls
67 db block gets
1687 consistent gets
11 physical reads
14208 redo size
838 bytes sent via SQL*Net to client
818 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
99 rows processed

Analysis of Oracle password expiry

Oracle password expiry is managed by the profile limit PASSWORD_LIFE_TIME, but based on the user$ table attributes ASTATUS and EXPTIME.

SQL> connect a/a
Connected.
SQL> select username, account_status, expiry_date from dba_users where username = 'A';

USERNAME                       ACCOUNT_STATUS                EXPIRY_DATE
------------------------------ ----------------------------- -----------------
A                              OPEN                          08 MAY 2010 10:22:52

SQL> select name, astatus, ptime, exptime from sys.user$ where name = 'A';

NAME                           ASTATUS    PTIME                EXPTIME
------------------------------ ---------- -------------------- ------------------
A                              0          09 NOV 2009 10:22:52

# So ASTATUS = O and EXPTIME are not set

SQL> ALTER PROFILE "DEFAULT" LIMIT PASSWORD_LIFE_TIME 1;

Profile altered.SQL> ALTER PROFILE "DEFAULT" LIMIT PASSWORD_GRACE_TIME 7;

Profile altered.


SQL> connect a/a
ERROR:
ORA-28002: the password will expire within 7 days


Connected.
SQL> select username, account_status, expiry_date from dba_users where username = 'A';

USERNAME                       ACCOUNT_STATUS              EXPIRY_DATE
------------------------------ --------------------------- -----------------
A                              EXPIRED(GRACE)              27 NOV 2009 09:17:11

SQL> select name, astatus, ptime, exptime from sys.user$ where name = 'A';

NAME                           ASTATUS  PTIME                EXPTIME
------------------------------ -------- -------------------- ------------------
A                              2        09 NOV 2009 10:22:52 27 NOV 2009 09:17:11

# So after we login we find the ASTATUS=2 and EXPTIME is set

SQL> ALTER PROFILE "DEFAULT" LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.

SQL> connect a/a
ERROR:
ORA-28002: the password will expire within 7 days


Connected.

# But we see that after we remove the password lifetime, the account is still expired
SQL> select username, account_status, expiry_date from dba_users where username = 'A';

USERNAME                       ACCOUNT_STATUS                EXPIRY_DATE
------------------------------ ----------------------------- -----------------
A                              EXPIRED(GRACE)                27 NOV 2009 09:17:11

SQL> select name, astatus, ptime, exptime from sys.user$ where name = 'A';

NAME                           ASTATUS  PTIME                EXPTIME
------------------------------ -------- -------------------- ------------------
A                              2        09 NOV 2009 10:22:52 27 NOV 2009 09:17:11


# and ASTATUS is still "2" and exptime is still set. So once expiry is set, changing the profile won't remove expiry.

SQL> alter user a identified by a;

User altered.

SQL> select username, account_status, expiry_date from dba_users where username = 'A';

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE
------------------------------ -------------------------------- -----------------
A                              OPEN

SQL> select name, astatus, ptime, exptime from sys.user$ where name = 'A';

NAME                           ASTATUS  PTIME                EXPTIME
------------------------------ -------- -------------------- ------------------
A                              0        20 NOV 2009 09:19:11 27 NOV 2009 09:17:11


# When we change the password the PTIME is updated, and the ASTATUS is reset to O, but EXPTIME is not changed.

# Conclusion - password expiry is controlled by the PASSWORD_LIFE_TIME profile limit, is activated at login, depends on a combination of ASTATUS and EXPTIME, and is reset by a password change. Changing the profile will not change ASTATUS. Changing the password resets ASTATUS, PTIME, but not EXPTIME.

Monday, November 16, 2009

Changing NLS session parameters changes the plan

# Developer complained that performance was slow after setting NLS_SORT=CZECH_AI
# Confirmed that it changed the plan to run full table scans.

SQL> select name from emp where name = 'a';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2061206800

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| EMP_NAME | 1 | 12 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("NAME"=U'a')

Note
-----
- dynamic sampling used for this statement (level=2)

SQL> alter session set NLS_COMP=LINGUISTIC;

Session altered.

SQL> alter session set NLS_SORT=CZECH_AI;

Session altered.

SQL> select name from emp where name = 'a';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 12 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(NLSSORT("NAME",'nls_sort=''CZECH_AI''')=HEXTORAW('14000100 ') )

Note
-----
- dynamic sampling used for this statement (level=2)

Friday, November 13, 2009

MAA for Active Dataguard

If you have some spare time you may want to read the MAA doc for Active Dataguard.

http://www.oracle.com/technology/deploy/availability/pdf/maa_wp_11gr1_activedataguard.pdf

Interesting things to note :-

1. a select statement to verify if a database is in Active Dataguard mode

SELECT 'Using Active Data Guard' ADG FROM V$MANAGED_STANDBY M, V$DATABASE D WHERE M.PROCESS LIKE 'MRP%' AND D.OPEN_MODE='READ ONLY';

2. a realtime select statement to verify Apply lag (the values in v$dataguard_stats are not realtime)

select scn_to_timestamp((select current_scn from v$database))-scn_to_timestamp((select current_scn from v$database@rtq_stby)) from dual;

3. A new statspack product for checking standby databases (it gets installed on the primary DB)

454848.1 Installing and Using Standby Statspack in 11gR1

Statspack cannot be executed on a standby due to its read-only nature therefore tuning the performance of the apply process involves manually collecting statistics.

In 11gR1, using the new Active Data Guard option, users can now use statspack from the primary database to collect data from a standby database that is opened read-only and performing recovery. The standby statspack is installed in a separate schema on the Primary database, STDBYPERF which is then propagated to the standby. This new user does not have DBA privileges and has no access to local V$ tables.

Checking Dataguard Transport Lag

# First check in the standby database for the curent value

1 select name, value from v$dataguard_stats
2* where name = 'transport lag'
SYS AS SYSDBA@pl01cfd1> /

NAME VALUE
----------------------------------------------------------------
transport lag +00 00:00:03


# Find Dataguard related metrics for Rac Databases.

select distinct column_label from mgmt_metrics where target_type = 'rac_database' and metric_name like 'dataguard%';

# find the raw stats for Transport Lag for database pl01sbt

select
t.target_name,
m.column_label,
mmr.collection_timestamp,
mmr.value
from
mgmt_targets t,
mgmt_metrics m,
mgmt_metrics_raw mmr
where t.target_guid = mmr.target_guid
and m.metric_guid = mmr.metric_guid
and m.column_label = 'Transport Lag (seconds)'
and t.target_name = 'pl01sbt';

# Find the 1 hour rollup stats for metric Transport Lag for database pl01sbt

select
t.target_name,
m.column_label,
mm1.rollup_timestamp,
mm1.value_average,
mm1.value_minimum,
mm1.value_maximum
from
mgmt_targets t,
mgmt_metrics m,
mgmt_metrics_1hour mm1
where t.target_guid = mm1.target_guid
and m.metric_guid = mm1.metric_guid
and m.column_label = 'Transport Lag (seconds)'
and t.target_name = 'pl01sbt';

Using x$logbuf_readhist to check for Dataguard Transport Lag

From the Oracle 11g Dataguard Handbook :-

Extract here :- http://www.mhprofessional.com/downloads/products/0071621113/CarpenterCh1.pdf

They say that if the LNS process cannot keep up with the LGWR process, then it switches from log buffer to online log, which introduces a transport lag. In 11g this can be detected by selecting from the x$logbuf_readhist;

>desc x$logbuf_readhist
Name Null? Type
----------------------------------------- --------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
BUFSIZE VARCHAR2(20)
RDMEMBLKS VARCHAR2(24)
RDDISKBLKS VARCHAR2(24)
HITRATE NUMBER
BUFINFO VARCHAR2(20)

select bufsize, rdmemblks, rddiskblks, hitrate from x$logbuf_readhist;

BUFSIZE RDMEMBLKS RDDISKBLKS HITRATE
-------------------- ------------------------ ------------------------ ----------
2665K 484903388 42044064 92
3197K 506308671 20638781 96
3730K 508354202 18593250 96
4263K 512373070 14574382 97
4796K 518101523 8845929 98
5329K 523136015 3811437 99
5862K 523347898 3599554 99
6395K 523556441 3391011 99
6928K 523634012 3313440 99
7461K 523704211 3243241 99
7994K 523734931 3212521 99
8526K 523771795 3175657 99
9059K 523792275 3155177 99
9592K 523808659 3138793 99
10125K 523818899 3128553 99
10658K 523822995 3124457 99

Thursday, November 12, 2009

Enable Active Standby on a database with Dataguard Broker

# Stop apply on the standby
dgmgrl
connect sys
edit database pl01ref set state=apply-off;
exit

# open the standby
sqlplus / a sysdba
alter database open;
exit

# start apply on the standby
dgmgrl
connect sys
edit database pl01ref set state=apply-on;
exit

Friday, November 6, 2009

Sqlnet Listener :- status READY, has 0 handler(s) for this service...

User reported Application servers were unable to connect to sqlnet listener service.
Listener was running, service was READY, but there were 0 handlers for the service.

Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx)(PORT=1560)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yyy)(PORT=1560)))

Service "TEST" has 1 instance(s).
Instance "zzz", status READY, has 0 handler(s) for this service...

According to Metalink note 885431.1 we can avoid this issue if our listeners use only one end-point. We need to listen on the VIP address, but don't need to listen on the HOST address.

E.g change

LISTENER_XXX =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myvip)(PORT = 1560)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1560)(IP = FIRST))
)
)

To

LISTENER_XXX =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myvip)(PORT = 1562)(IP = FIRST))
)
)

Thursday, November 5, 2009

ORA-01503, ORA-12720 when creating controlfile

# error on RAC database when creating controlfile

CREATE CONTROLFILE REUSE SET DATABASE "T01SBT" RESETLOGS FORCE LOGGING ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode

# solution

alter system set cluster_database=false scope=spfile;