ora-15032 ora-15040 ora-15042

ASM resilvering – or – how to recover your crashed cluster – Test no 3

http://blog.ronnyegner-consulting.de/2009/10/08/asm-resilvering-%E2%80%93-or-%E2%80%93-how-to-recover-your-crashed-cluster-%E2%80%93-test-no-3/

Some posts about RAID 5 vs. RAID 10

Recently there is an interesting discussion in Linkin about the impact of using RAID 5 or RAID 10 in Oracle RAC environment, here is the Linkin post: RAID 5 and RAID 10 intermixed. To understand more about differences and pros & cons between RAID 5 and 10, I googled and found some posts for further readings:

1. RAID 10 vs RAID 5: Performance, Cost, Space, and HA
2. RAID 5 vs RAID 10: Recommended RAID For Safety and Performance
3. RAID 0, RAID 1, RAID 5, RAID 10 Explained with Diagrams
4. Configuration Considerations for Improving PowerVault 220S Performance
5. More Spindles Means More IOPS? RAID10 vs RAID50

2 More Good Blog Posts from Enkitec about Oracle Database Appliance

Here are two more good blog posts from Enkitec engineers about the new Oracle Database Appliance:

1. Oracle Database Appliance (ODA) Installation / Configuration
2. Inside the Oracle Database Appliance - Part 1

Hopefully the budget request for Oracle Databae Appliance will be approved for the next fiscal year, then I would have a chance to play with it. 

Enkitec Blog: "Make bash scripts crontab-compatible" by Toby Marks

Here is another helpful blog post from Enkitec Blog:
Make bash scripts crontab-compatible

Paul Elbow's crsstat script: Improved Formatting of crs_stat on 10g and 11g

Today I found a very helpful script from Paul Elbow's blog to format crsctl output:
My crsstat script: Improved Formatting of crs_stat on 10g and 11g

ORA-12520: TNS:listener could not find available handler for requested type of server

Today I encountered a strange intermittent database connection problem when trying to connect to a database via SQL*Plus, the error I got is:
ORA-12520: TNS:listener could not find available handler for requested type of server

According to this Oracle note: Intermittent TNS-12516 or TNS-12519 Errors When Connecting Via Oracle Net [ID 240710.1] , it's likely that lack of server processes is the cause of the problem.  So I checked the system parameters settings of this database:
SQL> show parameters process;


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     150        


SQL> select * from v$resource_limit where resource_name in ('processes','sessions');


RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION                       LIMIT_VALUE
------------------------------ ------------------- --------------- ---------------------------------------- ----------------------------------------
processes                                       78             150        150                                      150
sessions                                        93             172        264                                      264


The process value is set to the default one by another DBA who created this database.  And also at the same time, there is an import running in the database, and it's using parallelism of 8. Do the settings of parallel related parameters have impact on generating this error? I don't know, I need to do more research about that.

SQL> show parameters parallel


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_degree_limit                string      CPU
parallel_degree_policy               string      MANUAL
parallel_execution_message_size      integer     16384
parallel_force_local                 boolean     FALSE
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     135
parallel_min_percent                 integer     0


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_min_servers                 integer     0
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_servers_target              integer     384
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0


Anyway, it seems the low value of "process" parameter a likely cause of this problem.

ORA-28002: the password will expire within 1 days

A user complained that his password was to expire in 1 day, so I changed the "PASSWORD_LIFE_TIME" to be "unlimted", but the user still got following message when logged into his account:


SQL> conn  user1/passwd@db1
C:\Users\pc1>sqlplus user1/passsd@db1


SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 1 16:22:54 2011


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


ERROR:
ORA-28002: the password will expire within 1 days


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


I was puzzled by this error message. I thought the "PASSWORD_LIFE_TIME" value changed to "unlimited" would resolve this problem. After searching Oracle Support, I found this note which explains why it still happens even after "PASSWORD_LIFE_TIME" value changed to "unlimited":

ORA-28002 On User Connection Immediately After PASSWORD_LIFE_TIME Changed [ID 162818.1]


"The PASSWORD_LIFE_TIME limit of a profile is measured from the last time an account's password was changed or the account creation time if the password has never been changed. These are the dates USER$.PTIME and USER$.CTIME respectively.

It is NOT measured from the current time, as may be initially thought.
Therefore, any accounts affected by the changed profile whose last password change time was more than PASSWORD_LIFE_TIME days ago immediately expire and enter their grace period on their next connection, issuing the ORA-28002 warning."

SQL> ALTER PROFILE test LIMIT PASSWORD_LIFE_TIME UNLIMITED;



This change only affects accounts who have not entered their grace period (and started getting ORA-28002 on connection). Accounts that have entered their grace period will have to change their passwords."


Here is what happend in one of my 11202 RAC database:
1 Check user account status:

SQL> select username, profile,account_status,expiry_date, created 
from dba_users 
where default_tablespace  not in ('SYSAUX','SYSTEM') 
--and expiry_date is not null 
and account_status not like '%LOCKED' order by expiry_date;

2 Change profile
SQL> alter profile default LIMIT PASSWORD_LIFE_TIME UNLIMITED;


3 Re-check account status

SQL> select username, profile,account_status,expiry_date, created 
from dba_users 
where default_tablespace  not in ('SYSAUX','SYSTEM') 
--and expiry_date is not null 
and account_status not like '%LOCKED' order by expiry_date;

Related Notes:

1. Oracle Password Management Policy [ID 114930.1]
2. BUG:1231172 - ORA-28003 WHEN CHANGING PASSWORD FOR A USER
3. NOTE:1079860.6 - ORA-28011 Password Expiry Date is Reached But Reset to NULL
4. NOTE:1083889.6 - ORA-00931: missing identifier when PASSWORD_VERIFY_FUNCTION = UNLIMITED
5. NOTE:124648.1 - ORA-28003, ORA-20001, ORA-20002, ORA-20003, ORA-20004 After Running utlpwdmg.sql
6. NOTE:139676.1 - ORA-28007: the password cannot be reused
   NOTE:139748.1 - Demonstrates the use of the new Oracle OCI8 OCIPasswordChange function (SCR 631)
7. NOTE:162818.1 - ORA-28002 On User Connection Immediately After PASSWORD_LIFE_TIME Changed
8. NOTE:260111.1 - How to Interpret the ACCOUNT_STATUS Column in DBA_USERS
9. NOTE:98481.1 - How to Keep the Same Password when Expiry Time is Reached and Change is Required