DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Friday, January 30, 2015

CPU Usage is 100%
Problem:
Oracle Process shows 100% CPU Usage continuously on Database version 11.2.0.1

Root Cause:
Most of the sessions are waiting on "latch: cache buffers chains" from long time even though those sessions are not processing any of the information.

Observation:
This is the bug in 11.2.0.1. Range of versions believed to be affected from 11.1 but BELOW 12.1 on Generic (all / most platforms affected) platform.

Work Around:
Clear "latch: cache buffers chains" sessions at regular interval based on identified condition.

Fix/Permanent Solution:
This issue is fixed on database version 11.2.0.2, So the database should upgrade to minimum 11.2.0.2.

Thursday, January 29, 2015

How To Properly Check System Memory Usage on Linux

To the casual observer, the output of some performance monitor utilities appear to report that system memory is almost all used up, with almost no free memory. For example:

$ free
             total       used       free     shared    buffers     cached
Mem:       3083104    2960224     122880       9708     131964    1491888
-/+ buffers/cache:    1336372    1746732
Swap:      2113532        196    2113336

DEFAULT CALCULATION
It seems that memory is almost completely used:

used / total == 2960224 / 3083104 * 100 == 96%

PROPER CALCULATION
Fortunately, this is not the case. The mistake is in not realizing Linux uses spare memory to cache disk data likely to be used again. The simplistic calculation ignores this; the proper calculation is:

(free+buffers+cached) / total * 100 == (122880+131964+1491888) / 3083104 == 56.7%

Taking this cached data into account, the calculation reveals the system is actively using less than 57^% and is certainly not under any memory stress.
Had this machine been under memory pressure, the cached category could rapidly be reused, and the buffers category could be reused after having been flushed to the backing store.


ORA-29516: Aurora assertion failure: Assertion failure at eox.c:232

Issue:
ORA-29516: Aurora assertion failure: Assertion failure at eox.c:232
Uncaught exception Root of all Java exceptions: java.lang.StackOverflowError

Solution:
Need to reduce the ulimit stack size. Have your linux administrator and set ulimit stack size to lower value and retest the Issue again.

Monday, January 19, 2015

Oracle 12c database creation using DBCA

Multitenant Architecture for Database Consolidation
Container databases are part of the 12c new feature called Multitenant Architecture.
The concept is that you have a Container database (CDB) at the root or top level which is in turn comprised of a number of pluggable databases (PDB).
Simplistically put, the CDB has the Oracle-supplied metadata like the Data Dictionary which is common to all the PDB’s in that Container database and each individual PDB contains the application data. We have the concept of common users which are common to all PDB’s in the CDB and each PDB can have its own distinct and individual users.
The rationale behind the muiltitenant architecture introduced in 12c is that instead of having hundreds of servers hosting hundreds of databases usually on different platforms and where in most cases the resources on each server are not fully utilized, go for database consolidation with the Container Database topology.
This hardware consolidation and sharing of the database background processes, memory, files etc reduce costs for hardware, storage, availability, as well as the administrative labor cost.
Administrative tasks like patch application and database upgrades are significantly less costly to the business and time consuming for the DBA’s because the patch or upgrade happens at the container level and not the individual database level.

Enterprise Manager Database Express
Enterprise Manager Database Control is no longer available in Oracle Database 12c. It has been replaced by Oracle Enterprise Manager Database Express.
It is scaled down version of Database Control and is an extremely light weight web-based tool for performing basic administration tasks and performance management. There is now a consolidation of several of the performance screens seen in the previous Database Control into what is referred to now as the database Performance Hub.




If we select the Advanced Mode in the Creation Mode screen, we will see the screens as shown below:














Plugin a Non-CDB database to a Container Database (CDB)

The Container database cdb12c originally has one pluggable database testdb1 (as well as the seed PDB). We will see how we can plugin the upgraded database testdb2 into the container database cdb12c.
Note that to plugin a non-CDB database into a CDB, the non-CDB database needs to be of version 12c as well. So existing 11g databases will need to be upgraded to 12c before they can be part of a 12c CDB.
Let us take a look at the steps involved.
The first thing we need to do is to connect to the non-CDB, and run the DBMS_PDB.DESCRIBE procedure to construct an XML file that contains the metadata which describes the non-CDB.
We need to run this procedure while the database is in a read only mode.
[oracle@orasql-001-dev backup]$ sqlplus sys as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 8 09:07:37 2013

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

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  801701888 bytes
Fixed Size                  2293496 bytes
Variable Size             314573064 bytes
Database Buffers          478150656 bytes
Redo Buffers                6684672 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

BEGIN
  DBMS_PDB.DESCRIBE(
    pdb_descr_file => '/home/oracle/testdb2.xml');
END;
/

PL/SQL procedure successfully completed.

Now shutdown and open the non-CDB in read write node.
We need to then check if the non-CDB is now ready for conversion into a Pluggable Database (PDD) which is going to be contained in the Container Database.
Connect to the CDB and run the DBMS_PDB.CHECK_PLUG_COMPATIBILITY package where we provide the location of the XML metadata file which was generated in the first step we executed.
SQL> SET SERVEROUTPUT ON
DECLARE
  compatible CONSTANT VARCHAR2(3) :=
SQL>   2    3      CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
  4             pdb_descr_file => '/home/oracle/testdb2.xml',
  5             pdb_name       => 'TESTDB2')
  6      WHEN TRUE THEN 'YES'
  7      ELSE 'NO'
  8  END;
  9  BEGIN
10    DBMS_OUTPUT.PUT_LINE(compatible);
11  END;
12  /
NO

PL/SQL procedure successfully completed.

Next we shutdown the non-CDB and plug it in to the CDB by converting it to a PLUGGABLE DATABASE.
SQL> CREATE PLUGGABLE DATABASE testdb2 using '/home/oracle/testdb2.xml'
  2   nocopy
  3  tempfile reuse;

Pluggable database created.

The XML file accurately describes the current locations of the files of the PDB
Since we are using the existing 12c non-CDB datafiles to create the CDB pluggable database , we will use the NOCOPY option.
If we want, we can leave the existing 12c non-cdb database and clone that to create a pluggable database
We can see that the testdb2 database is now a PDB and is in a mounted state.
SQL> select CON_ID, NAME, OPEN_MODE from V$PDBS;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 TESTDB1                        READ WRITE
         4 TESTDB2                        MOUNTED

SQL> select  PDB_NAME, DBID , CON_ID, STATUS  from CDB_PDBS;

PDB_NAME                   DBID     CON_ID STATUS
-------------------- ---------- ---------- -------------
TESTDB1               571254526          1 NORMAL
PDB$SEED             4061785545          1 NORMAL
TESTDB2              1141508778          1 NEW

SQL> ALTER SESSION SET CONTAINER =testdb2;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
TESTDB2

Now run the ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql script. This script must be run before the PDB can be opened for the first time.
After the script has been run open the PDB testdb2 in READ WRITE mode.
Now check the status.
SQL> alter session SET CONTAINER =CDB$ROOT;

Session altered.

SQL>  show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> select  PDB_NAME, DBID , CON_ID, STATUS  from CDB_PDBS;

PDB_NAME                   DBID     CON_ID STATUS
-------------------- ---------- ---------- -------------
TESTDB1               571254526          1 NORMAL
PDB$SEED             4061785545          1 NORMAL
TESTDB2              1141508778          1 NORMAL

3 rows selected.

Connect to the newly plugged-in PDB testdb2
[oracle@orasql-001-dev testdb2]$ sqlplus sh/sh@localhost:1525/testdb2
SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 8 11:04:11 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select count(*) from sales;
COUNT(*)
———-
918843

Oracle 12c Common and Local users - ORA-65096 ORA-65049

In 12c when you login to a CDB database you default to the CDB$ROOT container.  

[oracle@rac1 db_1]$ echo $ORACLE_SID
T12
[oracle@rac1 db_1]$ ps -ef | grep pmon
oracle   21182     1  0 13:21 ?        00:00:00 ora_pmon_T12

[oracle@rac1 db_1]$ sqlplus / as sysdba

SQL> show con_name

CON_NAME
-----------
CDB$ROOT

An attempt to create users in the CDB$ROOT container may result in ORA-65096 or ORA-65049 .

SQL> create user test identified by test;
create user test identified by test
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

oerr ora 65096
65096, 00000, "invalid common user or role name"
// *Cause:  An attempt was made to create a common user or role with a name
//          that wass not valid for common users or roles.  In addition to
//          the usual rules for user and role names, common user and role
//          names must start with C## or c## and consist only of ASCII
//          characters.
// *Action: Specify a valid common user or role name.
//

SQL> create user test identified by test container=current;
create user test identified by test container=current
                               *
ERROR at line 1:

ORA-65049: creation of local user or role is not allowed in CDB$ROOT

oerr ora 65049
65049, 00000, "creation of local user or role is not allowed in CDB$ROOT"
// *Cause:   An attempt was made to create a local user or role in CDB$ROOT.
// *Action:  If trying to create a common user or role, specify CONTAINER=ALL.

//

You first need to decide if you want a "common" user in the CDB which could be used to manage multiple PDBs, or a "local" user in only one PDB.  Check that you have set the container to the correct database before creating the user.

SQL> select PDB from v$services;

PDB
------------------------------
T12P2
T12P1
CDB$ROOT
CDB$ROOT
CDB$ROOT
CDB$ROOT

6 rows selected.

To create a "common" user in CDB$ROOT with name starting with c## :-


SQL> show con_name

CON_NAME
------------
CDB$ROOT

SQL> create user c##test identified by test;

User created.

To create a "local" user in PDB T12P1 :-

SQL> alter session set container=T12P1;

Session altered.

SQL> show con_name

CON_NAME
-----------
T12P1

SQL> create user test identified by test;

User created.

In PDB T12P1 we can see that DBA_USERS lists both the local user, and the common user.

SQL> select username from dba_users where username like '%TEST%';

USERNAME
-----------------------------------------------------------------
C##TEST
TEST

Oracle 12c New Features - Query CDB_PDB_HISTORY for Pluggable Database History

If you are interested in checking the history of PDBs, then view CDB_PDB_HISTORY is a good place to start.

SQL> SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME
FROM CDB_PDB_HISTORY WHERE CON_ID > 2 ORDER BY 5;  

DB_NAME    CON_ID PDB_NAME        OPERATION        OP_TIMESTA CLONED_FROM_PDB
---------- ------ --------------- ---------------- ---------- ---------------
SEEDDATA        5 PDB$SEED        UNPLUG           24-MAY-13
SEEDDATA        3 PDB$SEED        UNPLUG           24-MAY-13
SEEDDATA        4 PDB$SEED        UNPLUG           24-MAY-13
T12             5 PDB$SEED        PLUG             01-JUL-13  PDB$SEED
T12             3 PDB$SEED        PLUG             01-JUL-13  PDB$SEED
T12             4 PDB$SEED        PLUG             01-JUL-13  PDB$SEED
T12             5 PDB1            CREATE           01-JUL-13  PDB$SEED
T12             3 PDB1            CREATE           01-JUL-13  PDB$SEED
T12             4 PDB1            CREATE           01-JUL-13  PDB$SEED
T12             5 PDB1            UNPLUG           01-JUL-13
T12             3 PDB1            UNPLUG           01-JUL-13
T12             4 PDB1            UNPLUG           01-JUL-13
T12             5 PDB1            PLUG             01-JUL-13  PDB1
T12             3 PDB1            PLUG             01-JUL-13  PDB1
T12             4 PDB1            PLUG             01-JUL-13  PDB1
T12             5 PDB1            UNPLUG           01-JUL-13
T12             3 PDB1            UNPLUG           01-JUL-13
T12             4 PDB1            UNPLUG           01-JUL-13
T12             5 PDB1            PLUG             01-JUL-13  PDB1
T12             3 PDB1            PLUG             01-JUL-13  PDB1
T12             4 PDB1            PLUG             01-JUL-13  PDB1
T12             5 PDB1            UNPLUG           01-JUL-13
T12             3 PDB1            UNPLUG           01-JUL-13
T12             4 PDB1            UNPLUG           01-JUL-13
T12             4 PDB2            PLUG             01-JUL-13  PDB1
T12             5 PDB1            PLUG             01-JUL-13  PDB1
T12             3 PDB1            PLUG             01-JUL-13  PDB1
T12             5 PDB3            CLONE            01-JUL-13  PDB1

Oracle 12c - startup container AND pluggable databases.

In Oracle 12c we start the Container Database (CDB) the same as we started previous database versions.

[oracle@rac1 lib]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 27 14:47:35 2013

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size                  2289016 bytes
Variable Size             989856392 bytes
Database Buffers          654311424 bytes
Redo Buffers                7061504 bytes
Database mounted.
Database opened.

But then we find that the Pluggable Databases (PDBs) are still in "MOUNTED" state, so we will need to open them before we can login.

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           MOUNTED
PDB2                           MOUNTED
PDB3                           MOUNTED

From CDB$ROOT we can manage any PDB.  

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

To open one PDB :-

SQL> alter pluggable database PDB1 open;

Pluggable database altered.

To open ALL PDBS :-

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE
PDB3                           READ WRITE

Or we can move down to a PDB container to stop and start them individually.

SQL> alter session set container=PDB1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB1

SQL> shutdown;
Pluggable Database closed.
SQL> startup;
Pluggable Database opened.


Startup of PDBs can be automated using a startup trigger.

SQL> create or replace trigger Sys.After_Startup
                          after startup on database
begin
   execute immediate 'alter pluggable database all open';
end;
/  

Trigger created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1636814848 bytes
Fixed Size                  2288968 bytes
Variable Size             973079224 bytes
Database Buffers          654311424 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE
PDB3                           READ WRITE

Oracle 12c New Features - Alter Database Move Datafile

In previous versions of Oracle when DBAs needed to move datafiles to new storage, or migrate between ASM and filesystem they needed to first take the datafile offline, move the physical file, rename the file, recover it, and bring it online.  This usually required application outages, and to be safe DBAs often set the full tablespace read-only during this activity.  In Oracle 12c it is not necessary to take the datafile offline for the move.

In the following example I create tablespace TEST.

SQL> create tablespace test datafile '/u01/app/oracle/oradata/test_1.dbf' size 100M;

Tablespace created.

Then create a table and insert a record.

SQL> create table test (id integer) tablespace test;

Table created.

SQL> insert into test values (1);

1 row created.

SQL> commit;

Commit complete.

In this case I am moving it within the same filesystem, but we could also move to another filesystem or ASM.  I did not need to set tablespace to read-only, or take the datafile offline.

SQL> alter database move datafile '/u01/app/oracle/oradata/test_1.dbf' to '/u01/app/oracle/oradata/test_2.dbf';

Database altered.

SQL> select * from test;

        ID
----------

         1

In the alert log we see :-

Wed Jun 26 15:40:01 2013
create tablespace test datafile '/u01/app/oracle/oradata/test_1.dbf' size 100M
Completed: create tablespace test datafile '/u01/app/oracle/oradata/test_1.dbf' size 100M
alter database move datafile '/u01/app/oracle/oradata/test_1.dbf' to '/u01/app/oracle/oradata/test_2.dbf'
Wed Jun 26 15:41:50 2013
Moving datafile /u01/app/oracle/oradata/test_1.dbf (14) to /u01/app/oracle/oradata/test_2.dbf
Move operation committed for file /u01/app/oracle/oradata/test_2.dbf
Completed: alter database move datafile '/u01/app/oracle/oradata/test_1.dbf' to '/u01/app/oracle/oradata/test_2.dbf'

And on disk there is only the destination datafile.

[oracle@rac1 admin]$ cd /u01/app/oracle/oradata/
[oracle@rac1 oradata]$ ls -l test*
-rw-r----- 1 oracle oinstall 104865792 Jun 26 15:41 test_2.dbf

Oracle 12c New Features - SQL*Loader Express

Oracle 12c introduces Sql*Loader Express features, which allow users to run sqlldr with minimum configuration.  

The following example shows loading records into table EMPLOYEE from CSV file EMPLOYEE.dat without having to create a control file.


SQL> create table EMPLOYEE (id integer primary key, name varchar2(10));

Table created.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

[oracle@rac1 admin]$ more EMPLOYEE.dat
1,Adam
2,Ben
3,Colin
4,Dean
5,Evan
6,Frank
7,Greg
8,Hank
9,Ian
10,Jack
[oracle@rac1 admin]$ sqlldr test/test TABLE=EMPLOYEE

SQL*Loader: Release 12.1.0.1.0 - Production on Fri Jun 28 11:58:11 2013

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

Express Mode Load, Table: EMPLOYEE
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO
SQL*Loader-816: error creating temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for file EMPLOYEE.dat
ORA-01031: insufficient privileges
SQL*Loader-579: switching to direct path for the load
SQL*Loader-583: ignoring trim setting with direct path, using value of LDRTRIM
SQL*Loader-584: ignoring DEGREE_OF_PARALLELISM setting with direct path, using value of NONE
Express Mode Load, Table: EMPLOYEE
Path used:      Direct

Load completed - logical record count 10.

Table EMPLOYEE:
  10 Rows successfully loaded.

Check the log file:
  EMPLOYEE.log
for more information about the load.

[oracle@rac1 admin]$ ls EMPLOYEE*
EMPLOYEE.dat  EMPLOYEE.log

[oracle@rac1 admin]$ more EMPLOYEE.log

SQL*Loader: Release 12.1.0.1.0 - Production on Fri Jun 28 11:58:11 2013

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

Express Mode Load, Table: EMPLOYEE
Data File:      EMPLOYEE.dat
  Bad File:     EMPLOYEE_%p.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      External Table

Table EMPLOYEE, loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name                Position   Len   Term Encl Datatype
-------------------------- ---------- ----- ---- ---- ---------
ID                         FIRST      *     ,         CHARACTER
NAME                       NEXT       *     ,         CHARACTER

Generated control file for possible reuse:
OPTIONS(EXTERNAL_TABLE=EXECUTE, TRIM=LRTRIM)
LOAD DATA
INFILE 'EMPLOYEE'
APPEND
INTO TABLE EMPLOYEE
FIELDS TERMINATED BY ","
(
  ID,
  NAME
)
End of generated control file for possible reuse.

SQL*Loader-816: error creating temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for file EMPLOYEE.dat
ORA-01031: insufficient privileges

----------------------------------------------------------------
SQL*Loader-579: switching to direct path for the load
SQL*Loader-583: ignoring trim setting with direct path, using value of LDRTRIM
SQL*Loader-584: ignoring DEGREE_OF_PARALLELISM setting with direct path, using value of NONE
----------------------------------------------------------------

Express Mode Load, Table: EMPLOYEE
Data File:      EMPLOYEE.dat
  Bad File:     EMPLOYEE.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      Direct

Table EMPLOYEE, loaded from every logical record.
Insert option in effect for this table: APPEND

   Column Name        Position   Len   Term Encl Datatype
--------------------- ---------- ----- ---- ---- ----------------
ID                    FIRST      *     ,         CHARACTER
NAME                  NEXT       *     ,         CHARACTER

Generated control file for possible reuse:
OPTIONS(DIRECT=TRUE)
LOAD DATA
INFILE 'EMPLOYEE'
APPEND
INTO TABLE EMPLOYEE
FIELDS TERMINATED BY ","
(
  ID,
  NAME
)
End of generated control file for possible reuse.

The following index(es) on table EMPLOYEE were processed:
index TEST.SYS_C009860 loaded successfully with 10 keys

Table EMPLOYEE:
  10 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Bind array size not used in direct path.
Column array  rows :    5000
Stream buffer bytes:  256000
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:            10
Total logical records rejected:         0
Total logical records discarded:        0
Total stream buffers loaded by SQL*Loader main thread:        1
Total stream buffers loaded by SQL*Loader load thread:        0

Run began on Fri Jun 28 11:58:11 2013
Run ended on Fri Jun 28 11:58:12 2013

Elapsed time was:     00:00:01.27
CPU time was:         00:00:00.02


[oracle@rac1 admin]$ sqlplus test/test

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 28 12:05:49 2013

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

Last Successful login time: Fri Jun 28 2013 11:58:11 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL> select * from employee;

        ID NAME
---------- ----------
         1 Adam
         2 Ben
         3 Colin
         4 Dean
         5 Evan
         6 Frank
         7 Greg
         8 Hank
         9 Ian
        10 Jack


10 rows selected.