Showing posts with label DBA.Admin. Show all posts
Showing posts with label DBA.Admin. Show all posts

Friday, August 31, 2018

Oracle Database 18c Password Authentication with Microsoft Active Directory

Oracle database 18c introduces a new feature 'Integration of Active Directory Services with Oracle Database'. It gives us a way to authenticate and authorize users directly with Microsoft Active Directory. Oracle database users and roles can map directly to Active Directory users and groups without using Oracle Enterprise User Security (EUS) or another intermediate directory service. Here, I am going to demonstrate how to make it work step by step.
 
Environment:

  Database Server - Oracle Database 18c Enterprise Edition on Oracle Linux Server 7.5
  Microsoft Active Directory Server - Windows Server 2012 R2

Thursday, March 1, 2018

How to delete archive log files using PL/SQL


Although RMAN is always the first choice for most DBAs to delete archive log files, PL/SQL could be easier for this scenario,

User wants to switch an existing database from NOARCHIVELOG mode to ARCHIVELOG mode in order to increase the database availability because the database backup job has to shut down database when running in NOARCHIVELOG mode.

Saturday, February 10, 2018

12.2 database Alert log shows ORA-20001: Latest xml inventory is not loaded into table

While 12.2 database is being started by srvctl, the alert log shows following messages,
Unable to obtain current patch information due to error: 20001, ORA-20001: Latest xml inventory is not loaded into table
ORA-06512: at "SYS.DBMS_QOPATCH", line 777
ORA-06512: at "SYS.DBMS_QOPATCH", line 864
ORA-06512: at "SYS.DBMS_QOPATCH", line 2222
ORA-06512: at "SYS.DBMS_QOPATCH", line 740
ORA-06512: at "SYS.DBMS_QOPATCH", line 2247
===========================================================
Dumping current patch information
===========================================================
Unable to obtain current patch information due to error: 20001
===========================================================

Tuesday, February 21, 2017

Oracle Rules Manager 11.2.0.4.0 INVALID with invalid JAVA Classes

Oracle database 11.2.0.4 was created with INVALID component 'Oracle Rules Manager',  
system@DB01> select schema,comp_name,version,status from dba_registry;
SCHEMA                         COMP_NAME                                          VERSION                STATUS
------------------------------ -------------------------------------------------- ------------------------------
APEX_050000                    Oracle Application Express                         5.0.3.00.03            VALID
EXFSYS                         Oracle Expression Filter                           11.2.0.4.0             VALID
EXFSYS                         Oracle Rules Manager                               11.2.0.4.0             INVALID
XDB                            Oracle XML Database                                11.2.0.4.0             VALID
WMSYS                          Oracle Workspace Manager                           11.2.0.4.0             VALID
SYS                            Oracle Database Catalog Views                      11.2.0.4.0             VALID
SYS                            Oracle Database Packages and Types                 11.2.0.4.0             VALID
SYS                            JServer JAVA Virtual Machine                       11.2.0.4.0             VALID

Find out invalid objects owned by schema 'EXFSYS' which could INVALID the component Oracle Rules Manager,
system@DB01> select object_name,object_type,status
  2  from dba_objects
  3  where owner='EXFSYS' and status='INVALID';
OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
/53ad31cb_TagsLocator          JAVA CLASS          INVALID
/357abd97_XPathFilter          JAVA CLASS          INVALID

Check errors of found invalid JAVA CLASSes,
system@DB01> select name,text
  2  from dba_errors
  3  where owner='EXFSYS' and name in ('/53ad31cb_TagsLocator','/357abd97_XPathFilter');
NAME                      TEXT
------------------------- ------------------------------------------------------------
/357abd97_XPathFilter     ORA-29534: referenced object EXFSYS.oracle/expfil/xpath/TagsLocator could not be resolved
/53ad31cb_TagsLocator     ORA-29521: referenced name oracle/xml/parser/v2/DOMParser could not be found
/53ad31cb_TagsLocator     ORA-29521: referenced name oracle/xml/parser/v2/XMLParseException could not be found
/53ad31cb_TagsLocator     ORA-29521: referenced name oracle/xml/parser/v2/XMLDocument could not be found

Oracle Rules Manager is in INVALID status because the referenced object/names created by installing XDK cannot be resolved/found. EXFSYS (owner of Oracle Rules Manager) JAVA Classes are dependent on XDK. Therefore installing XDK will validate Oracle Rules Manager,
conn / as sysdba
@?/xdk/admin/initxml.sql
@?/rdbms/admin/utlrp.sql

Check component status,
system@DB01> select schema,comp_name,version,status from dba_registry;
SCHEMA                         COMP_NAME                                          VERSION                STATUS
------------------------------ -------------------------------------------------- ------------------------------
APEX_050000                    Oracle Application Express                         5.0.3.00.03            VALID
EXFSYS                         Oracle Expression Filter                           11.2.0.4.0             VALID
EXFSYS                         Oracle Rules Manager                               11.2.0.4.0             VALID
XDB                            Oracle XML Database                                11.2.0.4.0             VALID
WMSYS                          Oracle Workspace Manager                           11.2.0.4.0             VALID
SYS                            Oracle Database Catalog Views                      11.2.0.4.0             VALID
SYS                            Oracle Database Packages and Types                 11.2.0.4.0             VALID
SYS                            JServer JAVA Virtual Machine                       11.2.0.4.0             VALID


Monday, August 8, 2016

Automated SQL Tuning Task Failed with ORA-00600: [kpdbSwitchPreRestore: Txn]

Database: Oracle 12.1.0.2 RAC + Proactive Bundle Patch 12.1.0.2.160419
Operation System:  AIX 7.1


Automated SQL Tuning Task (SYS_AUTO_SQL_TUNING_TASK) failed and alert log shows errors:
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
2016-08-07 22:00:21.020000 -05:00
 <<Message truncated>>
2016-08-07 22:10:53.145000 -05:00
Errors in file /u01/app/oracle/diag/rdbms/cdb01/CDB01_1/trace/CDB01_1_j004_31719682.trc  (incident=109991) (PDBNAME=PDB01):
ORA-00600: internal error code, arguments: [kpdbSwitchPreRestore: txn], [0x7000101ECFA4F00], [0], [2685403136], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/cdb01/CDB01_1/incident/incdir_109991/CDB01_1_j004_31719682_i109991.trc
2016-08-07 22:11:11.467000 -05:00
Dumping diagnostic data in directory=[cdmp_201608071221111], requested by (instance=1, osid=85983625 (J004)), summary=[incident=109991].


The issue was not seen in non-cdb databases. According to Oracle Support explanation, the task is executed from a pluggable database but also accessing the container database through dblink to tune a remote query which triggers a bug. The bug can be fixed by patch 21283337 if it is available for your platform.

Before the patch is available or the Automated SQL Tuning Task is not really needed, as a workaround, the task (client_name is 'sql tuning advisor' in view dba_autotask_client) can be disabled,
system@PDB01> select client_name,status from dba_autotask_client;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto space advisor                                               ENABLED
auto optimizer stats collection                                  ENABLED
sql tuning advisor                                               ENABLED

system@PDB01> select client_name,operation_name,status from dba_autotask_operation;

CLIENT_NAME                              OPERATION_NAME                           STATUS
---------------------------------------- ---------------------------------------- --------
auto optimizer stats collection          auto optimizer stats job                 ENABLED
auto space advisor                       auto space advisor job                   ENABLED
sql tuning advisor                       automatic sql tuning task                ENABLED

system@PDB01> begin
  2  dbms_auto_task_admin.disable(client_name=>'SQL TUNING ADVISOR',
  3                               operation=>null,
  4                               window_name=>null);
  5  end;
  6  /

PL/SQL procedure successfully completed.

system@PDB01> select client_name,status from dba_autotask_client;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
sql tuning advisor                                               DISABLED
auto space advisor                                               ENABLED
auto optimizer stats collection                                  ENABLED

system@PDB01> select client_name,operation_name,status from dba_autotask_operation;

CLIENT_NAME                              OPERATION_NAME                           STATUS
---------------------------------------- ---------------------------------------- --------
auto optimizer stats collection          auto optimizer stats job                 ENABLED
auto space advisor                       auto space advisor job                   ENABLED
sql tuning advisor                       automatic sql tuning task                DISABLED


Usage notes for dbms_auto_task_admin.disable,

 * If operation and window_name are both NULL, the client is disabled.
 * If operation is not NULL, window_name is ignored and the operation is disabled
 * If operation is NULL and window_name is not NULL, the client is disabled in the specified window.

Note: If disabling the task as workaround, we have to disable it in all containers (PDB & root),

SYSTEM@PDB01> select client_name,status from dba_autotask_client;

CLIENT_NAME                                                      STATUS  
---------------------------------------------------------------- --------
auto space advisor                                               ENABLED
auto optimizer stats collection                                  ENABLED
sql tuning advisor                                               DISABLED

SYSTEM@PDB01> alter session set container=cdb$root;

Session altered.

SYSTEM@CDB$ROOT> select client_name,status from dba_autotask_client;

CLIENT_NAME                                                      STATUS  
---------------------------------------------------------------- --------
auto space advisor                                               ENABLED
auto optimizer stats collection                                  ENABLED
sql tuning advisor                                               ENABLED

SYSTEM@CDB$ROOT> select con_id,client_name,status from cdb_autotask_client;

    CON_ID CLIENT_NAME                                                      STATUS 
---------- ---------------------------------------------------------------- --------

         1 auto optimizer stats collection                                  ENABLED
         1 auto space advisor                                               ENABLED
         1 sql tuning advisor                                               ENABLED
         3 auto optimizer stats collection                                  ENABLED
         3 auto space advisor                                               ENABLED
         3 sql tuning advisor                                               DISABLED

6 rows selected.

SYSTEM@CDB$ROOT> exec dbms_auto_task_admin.disable('SQL TUNING ADVISOR',null,null);

PL/SQL procedure successfully completed.

SYSTEM@CDB$ROOT> select con_id,client_name,status from cdb_autotask_client;

    CON_ID CLIENT_NAME                                                      STATUS  
---------- ---------------------------------------------------------------- --------

         1 auto optimizer stats collection                                  ENABLED
         1 auto space advisor                                               ENABLED
         1 sql tuning advisor                                               DISABLED
         3 auto optimizer stats collection                                  ENABLED
         3 auto space advisor                                               ENABLED
         3 sql tuning advisor                                               DISABLED

6 rows selected.

Wednesday, April 29, 2015

Insufficient archive log space caused ORA-03113

Oracle 11g database was shut down for hardware maintenance. Unfortunately, the database can not be started after maintenance because of error ORA-03113.

$ srvctl sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 27 14:10:05 2015

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2226096 bytes
Variable Size 532678736 bytes
Database Buffers 276824064 bytes
Redo Buffers 23375872 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 33947688
Session ID: 749 Serial number: 3


Apparently, something happended while instance status is changed to OPEN from MOUNT. Check alert log file:

ARC3 started with pid=30, OS id=51183782
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Errors in file /u01/app/oracle/diag/rdbms/db01/DB01/trace/DB01_ora_45220056.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
ARCH: Error 19809 Creating archive log file to '+FRA2'
Errors in file /u01/app/oracle/diag/rdbms/db01/DB01/trace/DB01_ora_45220056.trc:
ORA-16038: log 3 sequence# 32464 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '+REDO1/db01/onlinelog/group_3.310.857090421'
ORA-00312: online log 3 thread 1: '+REDO2/db01/onlinelog/group_3.310.857090421'
USER (ospid: 45220056): terminating the instance due to error 16038


Instance is terminated due to error 16038: log 3 sequence# 32464 cannot be archived. Redo log cannot be archived because the recovery area is 100% used.

Backup archive log and release space by running:

$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Apr 27 14:17:52 2015

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

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area 835104768 bytes

Fixed Size 2226096 bytes
Variable Size 532678736 bytes
Database Buffers 276824064 bytes
Redo Buffers 23375872 bytes

RMAN> backup archivelog all delete all input;

... ...


Try to open database:

RMAN> alter database open;

database opened


Database is opened successfully.

Thursday, January 29, 2015

Database link cannot be dropped because of ORA-02024

When dropping database link got following error:
ORA-02024: database link not found.
SQL> select * from user_db_links;

DB_LINK USERNAME PASSWORD HOST CREATED
----------------- ------------- ---------- ----------- ---------
REFRESH_DBLINK SYSTEM DBPROD 15-OCT-13

SQL> drop database link REFRESH_DBLINK;
drop database link REFRESH_DBLINK
*
ERROR at line 1:
ORA-02024: database link not found

Can not find an existing database link? What if create a new link with same name?
SQL> create database link REFRESH_DBLINK
2 connect to system identified by Ax356 using 'DBPROD';

Database link created.

SQL> select * from user_db_links;

DB_LINK USERNAME PASSWORD HOST CREATED
------------------------ ------------- ---------- ----------- ---------
REFRESH_DBLINK SYSTEM DBPROD 15-OCT-13
REFRESH_DBLINK.WORLD SYSTEM DBPROD 29-JAN-15

The name of new database link is appended with '.WORLD'. Did more test and found that all new created database link will be added suffix '.WORLD' in the name. Try DROP DATABASE LINK to see what is going to happen:
SQL> drop database link REFRESH_DBLINK;

Database link dropped.

SQL> select * from user_db_links;

DB_LINK USERNAME PASSWORD HOST CREATED
------------------------ ------------- ---------- ----------- ---------
REFRESH_DBLINK SYSTEM DBPROD 15-OCT-13

See, I was dropping REFRESH_DBLINK, but REFRESH_DBLINK.WORLD was dropped. I always get REFRESH_DBLINK.WORLD dropped no matter whether I include '.WORLD' in the DROP DATABASE LINK command. Why did we get the weird term '.WORLD'? It was popular long time ago as default database domain and often found in global_name & db_domain of the database:
SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string DBPROD

SQL> show parameter db_domain

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------
DBPROD.WORLD

It does be a part of database global_name. Does domain part in global_name affect database link name space? Unfortunately, it does. Delete the domain part from global_name:

 
SQL> alter database rename global_name to DBPROD;

Database altered.

SQL> select * from global_name;

GLOBAL_NAME
------------------------------------------------
DBPROD.WORLD

Interesting, the ALTER DATABASE command does not help. Let us manually change global_name bypass ALTER DATABASE command,
Note: Take a complete consistent backup of the database before you continue:
SQL> conn / as sysdba
Connected.

SQL> select name,value$ from props$ where GLOBAL_name='GLOBAL_DB_NAME';

NAME VALUE$
------------------------------ --------------------------------------------------
GLOBAL_DB_NAME DBPROD.WORLD

SQL> update props$ set value$='DBPROD' where name='GLOBAL_DB_NAME';

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from global_name;

GLOBAL_NAME
---------------------
DBPROD

Oracle never suggests direct update on props$. We are not supposed to do that. Hmmm, today is special :)

Drop the database link:
SQL> connect system

SQL> drop datbaetabase link REFRESH_DBLINK;

Database link dropped.

SQL> select * from user_db_links;

no rows selected

Finally, database link is really dropped.

Monday, January 26, 2015

TNSPING of 11g RAC database got very slow response

Business users complain it took several minutes to log into database. It used to take several seconds.

Confirmed informations as following:

Server: Solaris 10 SPARC + Oracle Database Server 11.2.0.3 RAC
Client: windows 7 + Oracle Client 11.2.0.3

Testing connectivity from workstation:
C:\>tnsping RACPD

TNS Ping Utility for 32-bit Windows: Version 11.2.0.3.0 - Production on 26-JAN-2015 10:49:15

Copyright (c) 1997, 2011, Oracle. All rights reserved.

Used parameter files:
c:\app\oracle\product\11.2.0\client11203\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host-scan.dbaplus.ca)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=RACPD)))
OK (120000 msec)

Feel like the SCAN listener(s) is/are alive and there is network issue. If it is network problem, the issue should be reproduced on all three IP addresses bound with SCAN name (host-scan.dbaplus.ca). Let's find IPs first:
C:\>nslookup host-scan.dbaplus.ca
Server: ns01.dbaplus.ca
Address: 10.10.2.253

Name: host-scan.dbaplus.ca
Addresses: 10.10.2.30
10.10.2.31
10.10.2.29

Checking the IP availability:
C:\>ping 10.10.2.30

Pinging 10.10.2.30 with 32 bytes of data:
Reply from 10.10.2.30: bytes=32 time=5ms TTL=252
Reply from 10.10.2.30: bytes=32 time<1ms TTL=252
Reply from 10.10.2.30: bytes=32 time<1ms TTL=252
Reply from 10.10.2.30: bytes=32 time<1ms TTL=252

Ping statistics for 10.10.2.30:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 5ms, Average = 1ms

C:\>ping 10.10.2.31

Pinging 10.10.2.31 with 32 bytes of data:
Request timed out.
Request timed out.
Request timed out.
Request timed out.

Ping statistics for 10.10.2.31:
Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),

C:\>ping 10.10.2.29

Pinging 10.10.2.29 with 32 bytes of data:
Request timed out.
Request timed out.
Request timed out.
Request timed out.

Ping statistics for 10.10.2.29:
Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),

Two of three SCAN virtual IPs are timed out, the other one (10.10.2.30) is working. It is not network problem. Log onto database servers:
$ . oraenv
ORACLE_SID = [grid] ? +ASM1
The Oracle base has been set to /u01/app/oracle
$
$ crsctl status res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.FRA.dg
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.LISTENER.lsnr
ONLINE ONLINE host01
ONLINE OFFLINE host02
ora.asm
ONLINE ONLINE host01 Started
ONLINE ONLINE host02 Started
ora.gsd
OFFLINE OFFLINE host01
OFFLINE OFFLINE host02
ora.net1.network
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.ons
ONLINE ONLINE host01
ONLINE ONLINE host02
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE host02
ora.LISTENER_SCAN2.lsnr
1 ONLINE OFFLINE
ora.LISTENER_SCAN3.lsnr
1 ONLINE OFFLINE
ora.racdb.db
1 ONLINE ONLINE host01 Open
2 ONLINE ONLINE host02 Open
ora.host01.vip
1 ONLINE ONLINE host01
ora.host02.vip
1 ONLINE UNKNOWN host01
ora.racdb.db
1 ONLINE ONLINE host01 Open
2 ONLINE ONLINE host02 Open
ora.scan1.vip
1 ONLINE ONLINE host02
ora.scan2.vip
1 ONLINE UNKNOWN host01
ora.scan3.vip
1 ONLINE UNKNOWN host01

SCAN VIP scan2 & scan3 are in UNKNOWN status, corresponding SCAN listeners LISTENER_SCAN2 & LISTENER_SCAN3 are OFFLINE.

When I connect to database from workstation, Oracle client will work as following:
1. Ask DNS server for IPs for the SCAN name host-scan.dbaplus.ca, and get three IPs
2. Choose one of them to connect. If I am lucky, the one should be SCAN1 and I will not find any issues. Actually, I was not. scan2 (or scan3) was chosen and the request waited for response from server until time out because the SCAN VIP is not ONLINE, then failed over to another SCAN VIP scan3 (or scan2) or scan1. The worst thing is failover to scan3, it will get timeout again and have to fail over finally to scan1.
3. No matter where it starts, SCAN VIP scan1 will be the final and only choice for the connection. How fast the connection can be established depends on how fast the ONLINE SCAN VIP scan1 is chosen.

To fix the issue, try to start them:
$ srvctl start scan_listener -i 2
$ srvctl start scan_listener -i 3
$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node host02
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node host01
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node host01

$ crsctl status res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.FRA.dg
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.LISTENER.lsnr
ONLINE ONLINE host01
ONLINE OFFLINE host02
ora.asm
ONLINE ONLINE host01 Started
ONLINE ONLINE host02 Started
ora.gsd
OFFLINE OFFLINE host01
OFFLINE OFFLINE host02
ora.net1.network
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.ons
ONLINE ONLINE host01
ONLINE ONLINE host02
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE host02
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE host01
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE host01
ora.racdb.db
1 ONLINE ONLINE host01 Open
2 ONLINE ONLINE host02 Open
ora.host01.vip
1 ONLINE ONLINE host01
ora.host02.vip
1 ONLINE UNKNOWN host01
ora.racdb.db
1 ONLINE ONLINE host01 Open
2 ONLINE ONLINE host02 Open
ora.scan1.vip
1 ONLINE ONLINE host02
ora.scan2.vip
1 ONLINE ONLINE host01
ora.scan3.vip
1 ONLINE ONLINE host01

All SCAN VIP and SCAN listeners are ONLINE. Back to workstation and test the connection:
C:\>tnsping RACPD

TNS Ping Utility for 32-bit Windows: Version 11.2.0.3.0 - Production on 26-JAN-2015 10:49:15

Copyright (c) 1997, 2011, Oracle. All rights reserved.

Used parameter files:
c:\app\oracle\product\11.2.0\client11203\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host-scan.dbaplus.ca)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=RACPD)))
OK (10 msec)

'10 msec' response time is not bad. The issue got fixed.

Monday, January 19, 2015

Setting CURRENT_SCHEMA caused error 'ORA-01031: insufficient privileges'

Question: Why did I get 'ORA-01031: insufficient privileges' when running 'create table' command to create table ? I do have 'CREATE TABLE' privilege granted.

Answer: the CURRENT_SCHEMA of your session is not expected account of yours.

The database user devuser is granted 'CREATE TABLE' privilege:
evuser@ORADB> select * from user_sys_privs;

USERNAME PRIVILEGE ADM
-------------------- ---------------------------------------- ---
DEVUSER CREATE TABLE NO
DEVUSER CREATE SESSION NO

But he got ORA-01031 when running 'create table'
devuser@ORADB> create table tb01(sno number);
create table tb01(sno number)
*
ERROR at line 1:
ORA-01031: insufficient privileges

And did not get error when creating table with schema specified in the statement:
devuser@ORADB> create table devuser.tb01(sno number);

Table created.

Looks like the 'create table' without schema specified is trying to create table in another user's schema. Find out who the another user is:
devuser@ORADB> select sys_context('userenv','current_schema') as current_schema from dual;

CURRENT_SCHEMA
------------------------------
DEVOWNER

It is not expected user name 'devuser'. Try to change back:
devuser@ORADB> alter session set current_schema=devuser;

Session altered.

devuser@ORADB> select sys_context('userenv','current_schema') as current_schema from dual;

CURRENT_SCHEMA
------------------------------
DEVUSER

Test if 'create table' works:
devuser@ORADB> create table tb02(sno number);

Table created.

See, current_schema accounts for it!

 

 

 

 

 

Friday, January 9, 2015

Relink Oracle Grid Infrastructure Standalone (Oracle Restart) home and Database home after OS is upgraded from AIX 6.1 to 7.0

Oracle Database 11.2.0.3
Operating System AIX 7.0
The server system is upgraded from AIX 6.1 to 7.0, generally IBM guarantees operating system binary compatibility, therefore, no relink of the Oracle
software is required after the upgrade. However, Oracle recommends performing manual relinking of Oracle Home binaries after OS upgrade.

1.Stop Oracle databases (as owner of Oracle Database Home):
$ id  oracle
uid=207(oracle) gid=110(oinstall) groups=60005(dba),60015(asmdba)
$ . oraenv
ORACLE_SID = [oracle] ? ORADB
The Oracle base has been set to /u01/app/oracle
$ srvctl stop home -o /u01/app/oracle/product/11.2.0/db11203 -s stophome.txt -t immediate

2.Stop the OHAS stack (as owner of Oracle Restart Home):
$ id 
uid=208(grid) gid=110(oinstall) groups=60005(dba),60014(asmadmin),60015(asmdba)
$ . oraenv
ORACLE_SID = [grid] ? +ASM
The Oracle base has been set to /u01/app/grid
$ crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'host01'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'host01'
CRS-2673: Attempting to stop 'ora.FLASH.dg' on 'host01'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'host01'
CRS-2677: Stop of 'ora.DATA.dg' on 'host01' succeeded
CRS-2677: Stop of 'ora.FLASH.dg' on 'host01' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'host01'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'host01' succeeded
CRS-2677: Stop of 'ora.asm' on 'host01' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'host01'
CRS-2677: Stop of 'ora.cssd' on 'host01' succeeded
CRS-2673: Attempting to stop 'ora.evmd' on 'host01'
CRS-2677: Stop of 'ora.evmd' on 'host01' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'host01' has completed
CRS-4133: Oracle High Availability Services has been stopped.

3. Remove audit files from Oracle Restart Home if applicable
SQL> show parameter  audit_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ----------------------------------------------
audit_file_dest string /u01/app/grid/product/11.2.0/gi11203/rdbms/audit

Audit files are saved in Oracle Restart Home, we have to move (backup) audit files out of the home. Otherwise, the following 'unlock' step will take long time.
$ mv /u01/app/grid/product/11.2.0/gi11203/rdbms/audit/*.aud /u01/app/grid/backup

4. Connect as root user and unlock the Oracle Restart installation as follows:
# . oraenv
ORACLE_SID = [root] ? +ASM
The Oracle base has been set to /u01/app/grid
#
# echo $ORACLE_HOME
/u01/app/grid/product/11.2.0/gi11203
#
# $ORACLE_HOME/crs/install/roothas.pl -unlock
Using configuration parameter file: /u01/app/grid/product/11.2.0/gi11203/crs/install/crsconfig_params
Successfully unlock /u01/app/grid/product/11.2.0/gi11203

5. Relink the Oracle Restart installation (as owner of Oracle Restart Home):
$ . oraenv
ORACLE_SID = [grid] ? +ASM
The Oracle base remains unchanged with value /u01/app/grid
$
$ ls -l $ORACLE_HOME/rdbms/lib/config.o
-rw-r--r-- 1 grid oinstall 764 Oct 18 2013 /u01/app/grid/product/11.2.0/gi11203/rdbms/lib/config.o
$
$ mv $ORACLE_HOME/rdbms/lib/config.o $ORACLE_HOME/rdbms/lib/config.o.bak
$
$ ls -l $ORACLE_HOME/rdbms/lib/config.o*
-rw-r--r-- 1 grid oinstall 764 Oct 18 2013 /u01/app/grid/product/11.2.0/gi11203/rdbms/lib/config.o.bak
$
$ $ORACLE_HOME/bin/relink all
writing relink log to: /u01/app/grid/product/11.2.0/gi11203/install/relink.log
$

6. Check the relink log:
$ cat /u01/app/grid/product/11.2.0/gi11203/install/relink.log
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 12800 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2015-01-08_02-01-58PM. Please wait ...Please find the log file at /u01/app/grid/product/11.2.0/gi11203/install/relinkActions2015-01-08_02-02-06-PM.log
... ...
ld: 0711-224 WARNING: Duplicate symbol: .aio_nwait_timeout64
... ...
ld: 0711-773 WARNING: Object /u01/app/grid/product/11.2.0/gi11203/lib/libxml11.a[lgx.o], imported symbol id__Q2_3std8numpunctXTc_
Symbol was expected to be local. Extra instructions
are being generated to reference the symbol.
... ...
ld: 0711-783 WARNING: TOC overflow. TOC size: 185792 Maximum size: 65536
... ...

Oops. Thousands of 'ld: 0711-773' and 'ld: 0711-783' warning messages! Don'g worry, that's expected while Oracle home is relinked on AIX, they can be safely ignored.
We can find new binary file created:
$ ls -l  /u01/app/grid/product/11.2.0/gi11203/bin/oracle*
-rwsr-s--x 1 grid oinstall 265201550 Jan 8 14:04 /u01/app/grid/product/11.2.0/gi11203/bin/oracle
-rwsr-s--x 1 grid oinstall 265345822 Jun 27 2014 /u01/app/grid/product/11.2.0/gi11203/bin/oracleO

7. Connect as root user and lock back the Oracle Restart installation
# $ORACLE_HOME/crs/install/roothas.pl -patch
Using configuration parameter file: /u01/app/grid/product/11.2.0/gi11203/crs/install/crsconfig_params
CRS-4123: Oracle High Availability Services has been started.

8. Verify OHAS services (As owner of Oracle Restart Home):
$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE host01
ora.FLASH.dg
ONLINE ONLINE host01
ora.LISTENER.lsnr
ONLINE ONLINE host01
ora.asm
ONLINE ONLINE host01 Started
ora.ons
OFFLINE OFFLINE host01
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
1 ONLINE ONLINE host01
ora.diskmon
1 OFFLINE OFFLINE
ora.evmd
1 ONLINE ONLINE host01
ora.oradb.db
1 OFFLINE OFFLINE Instance Shutdown

9. Relink Database home  (as owner of Database home)
$ . oraenv
ORACLE_SID = [oracle] ? ORADB
The Oracle base remains unchanged with value /u01/app/oracle
$
$ ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle asmadmin 287983743 Jun 27 2014 /u01/app/oracle/product/11.2.0/db11203/bin/oracle
$
$ $ORACLE_HOME/bin/relink all
writing relink log to: /u01/app/oracle/product/11.2.0/db11203/install/relink.log
$
$ ls -l $ORACLE_HOME/bin/oracle*
-rwsr-s--x 1 oracle oinstall 287912357 Jan 8 15:17 /u01/app/oracle/product/11.2.0/db11203/bin/oracle
-rwsr-s--x 1 oracle asmadmin 287983743 Jun 27 2014 /u01/app/oracle/product/11.2.0/db11203/bin/oracleO

10. Start databases:
$ srvctl start home -o /u01/app/oracle/product/11.2.0/db11203 -s stophome.txt

 References:
"Relink All" On AIX Raises Many Warnings And "ld: 0706-032 Linking mode is not specified" for Liborasdk (Doc ID 809153.1)

Friday, December 26, 2014

ASMB process crashed the instance after 'PMON failed to acquire latch'

Environment:
Oracle Database: Enterprise Edition 11.2.0.2.12
Operating System:  AIX 6.1

Symptom:

Database alert log:
Fri Dec 26 07:23:04 2014
PMON failed to acquire latch, see PMON dump
Fri Dec 26 07:24:04 2014
PMON failed to acquire latch, see PMON dump
Fri Dec 26 07:25:36 2014
PMON failed to acquire latch, see PMON dump
Fri Dec 26 07:26:36 2014
PMON failed to acquire latch, see PMON dump
Fri Dec 26 07:28:07 2014
PMON failed to acquire latch, see PMON dump
Fri Dec 26 07:28:52 2014
NOTE: ASMB terminating
Errors in file /u01/app/oracle/diag/rdbms/oradb/ORADB/trace/ORADB_asmb_5439902.trc:
ORA-15064: communication failure with ASM instance
ORA-03135: connection lost contact
Process ID:
Session ID: 152 Serial number: 9
Errors in file /U01/app/oracle/diag/rdbms/oradb/ORADB/trace/ORADB_asmb_5439902.trc:
ORA-15064: communication failure with ASM instance
ORA-03135: connection lost contact
Process ID:
Session ID: 152 Serial number: 9
ASMB (ospid: 5439902): terminating the instance due to error 15064
Fri Dec 26 07:28:52 2014

PMON trace:
*** 2014-12-26 07:23:04.963
Location from where latch is held: kgh.h LINE:6387 ID:kghalo:
Context saved from call: 0
state=busy [holder orapid=16] wlstate=free [value=0]
waiters [orapid (seconds since: put on list, posted, alive check)]:
64 (103, 1419596584, 0)
... ...
61 (4, 1419596584, 0)
waiter count=17
gotten 38993536 times wait, failed first 285766 sleeps 71131
gotten 0 times nowait, failed: 0
Short stack dump:
... ...
possible holder pid = 16 ospid=5439902
----------------------------------------
SO: 0x7000002fec6df78, type: 2, owner: 0x0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x7000002fec6df78, name=process, file=ksu.h LINE:12451 ID:, pg=0
(process) Oracle pid:16, ser:1, calls cur/top: 0x7000002ff83fd00/0x7000002ff83fd00
flags : (0x6) SYSTEM
flags2: (0x0), flags3: (0x0)
intr error: 0, call error: 0, sess error: 0, txn error 0
intr queue: empty
ksudlp FALSE at location: 0
(post info) last post received: 0 0 35
last post received-location: ksr2.h LINE:603 ID:ksrpublish
last process to post me: 7000002fec731a8 2 0
last post sent: 1051 0 2
last post sent-location: ksl2.h LINE:2293 ID:kslpsr
last process posted by me: 7000002fec69db8 1 6
(latch info) wait_event=0 bits=84
holding (efd=4) 700000000107940 Child shared pool level=7 child#=1
Location from where latch is held: kgh.h LINE:6387 ID:kghalo:
Context saved from call: 0
state=busy [holder orapid=16] wlstate=free [value=0]
waiters [orapid (seconds since: put on list, posted, alive check)]:
64 (103, 1419596584, 0)
... ...
61 (4, 1419596584, 0)
waiter count=17
holding (efd=4) 700000000047900 ASM map headers level=2
Location from where latch is held: kffm2.h LINE:306 ID:kffmAllocate:
Context saved from call: 0
state=busy [holder orapid=16] wlstate=free [value=0]
Process Group: DEFAULT, pseudo proc: 0x7000002fee6c858
O/S info: user: oragrid, term: UNKNOWN, ospid: 5439902
OSD pid info: Unix process pid: 5439902, image: oracle@host01.dbaplus.ca (ASMB)

ASMB trace:
*** 2014-12-26 07:28:52.409
NOTE: ASMB terminating
ORA-15064: communication failure with ASM instance
ORA-03135: connection lost contact
Process ID:
Session ID: 152 Serial number: 9
error 15064 detected in background process
ORA-15064: communication failure with ASM instance
ORA-03135: connection lost contact
Process ID:
Session ID: 152 Serial number: 9
kjzduptcctx: Notifying DIAG for crash event
----- Abridged Call Stack Trace -----
ksedsts()+644<-kjzdssdmp()+444<-kjzduptcctx()+272<-kjzdicrshnfy()+96<-ksuitm()+1284<-ksbrdp()+4508<-opirip()+1216<-opidrv()+1156<-sou2o()+192<-opimai_real()+308<-ssthrdmain()+340<-main()+216<-__start()+112
----- End of Abridged Call Stack Trace -----

*** 2014-12-26 07:28:52.412
ASMB (ospid: 5439902): terminating the instance due to error 15064
ksuitm: waiting up to [5] seconds before killing DIAG(8519832)

AWR report:
Cache Sizes                       Begin        End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 6,464M 6,464M Std Block Size: 16K
Shared Pool Size: 5,152M 5,152M Log Buffer: 7,172K

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 97.27 In-memory Sort %: 100.00
Library Hit %: 73.34 Soft Parse %: 63.01
Execute to Parse %: 45.26 Latch Hit %: 99.74
Parse CPU to Parse Elapsd %: 4.75 % Non-Parse CPU: 92.21


Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
latch: shared pool 836 866 1035 38.4 Concurrenc
db file sequential read 227,015 281 1 12.5 User I/O
DB CPU 280 12.4
latch free 6 161 26794 7.1 Other
library cache lock 3 89 29759 4.0 Concurrenc

Check duplicate SQL:
select PLAN_HASH_VALUE,count(*) from v$sql
where EXECUTIONS=1 group by PLAN_HASH_VALUE order by 2 desc;

251958376 34281
3946703927 33694
1997326701 2603
3410233426 1221
3460305098 304
... ...
3150591124 1

select substr(sql_text,1,50),count(1) from v$sql
where PLAN_HASH_VALUE=251958376 group by substr(sql_text,1,50);

Obviously,  the application is not designed with consideration of bind variables, the duplicate SQLs (using literals instead of bind variables) caused massive shared pool fragmentation.

Therefore, ASMB process can not find a suitable sized chunk of memory before it was considered unresponsive and thereafter killed.

Workaround:

Since application can not be changed (system too old to find developer), tried CURSOR_SHARING=FORCE and no crash happened after that.