12c

MGMTDB: Grid Infrastructure Management Repository

MGMTDB is new database instance which is used for storing Cluster Health Monitor (CHM) data. In 11g this was being stored in berkley database but starting Oracle database 12c it is configured as  Oracle Database Instance.
In 11g, .bdb files were stored in $GRID_HOME/crf/db/hostname and used to take up lot of space (>100G) due to bug in 11.2.0.2

During 12c Grid infrastructure installation, there is option to configure Grid Infrastructure Management Repository.

grid_management_db

If you choose YES, then you will see instance -MGMTDB running on one of the node on your cluster.

[oracle@oradbdev02]~% ps -ef|grep mdb_pmon
oracle    7580     1  0 04:57 ?        00:00:00 mdb_pmon_-MGMTDB

This is a Oracle single instance which is being managed by Grid Infrastructure and fails over to surviving node if existing node crashes.You can identify the current master using below command

-bash-4.1$ oclumon manage -get MASTER

Master = oradbdev02

This DB instance can be managed using srvctl commands. Current master can also be identified using status command

$srvctl status mgmtdb 
Database is enabled
Instance -MGMTDB is running on node oradbdev02

We can look at mgmtdb config using

$srvctl config mgmtdb
Database unique name: _mgmtdb
Database name: 
Oracle home: /home/oragrid
Oracle user: oracle
Spfile: +VDISK/_mgmtdb/spfile-MGMTDB.ora
Password file: 
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: -MGMTDB
Type: Management

Replace config with start/stop to start/stop database.
Databases files for repository database are stored in same location as OCR/Voting disk

SQL> select file_name from dba_data_files union select member file_name from V$logfile;

FILE_NAME
------------------------------------------------------------
+VDISK/_MGMTDB/DATAFILE/sysaux.258.819384615
+VDISK/_MGMTDB/DATAFILE/sysgridhomedata.261.819384761
+VDISK/_MGMTDB/DATAFILE/sysmgmtdata.260.819384687
+VDISK/_MGMTDB/DATAFILE/system.259.819384641
+VDISK/_MGMTDB/DATAFILE/undotbs1.257.819384613
+VDISK/_MGMTDB/ONLINELOG/group_1.263.819384803
+VDISK/_MGMTDB/ONLINELOG/group_2.264.819384805
+VDISK/_MGMTDB/ONLINELOG/group_3.265.819384807

We can verify the same using oclumon command

-bash-4.1$ oclumon manage -get reppath

CHM Repository Path = +VDISK/_MGMTDB/DATAFILE/sysmgmtdata.260.819384687

Since this is stored at same location as Voting disk, if you have opted for configuring Management database, you will need to use voting disk with size >5G (3.2G+ is being used by MGMTDB). During GI Installation ,I had tried adding voting disk of 2G but it failed saying that it is of insufficient size. Error didnot indicate that its needed for Management repository but now I think this is because of repository sharing same location as OCR/Voting disk.
Default (also Minimum) size for CHM repository is 2048 M . We can increase respository size by issuing following command

-bash-4.1$ oclumon manage -repos changerepossize 4000
The Cluster Health Monitor repository was successfully resized.The new retention is 266160 seconds.

This command internally runs resize command on datafile and we can see that it changed datafile size from 2G to 4G

SQL> select file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_data_files;

FILE_NAME					   BYTES/1024/1024 MAXBYTES/1024/1024 AUT
-------------------------------------------------- --------------- ------------------ ---
+VDISK/_MGMTDB/DATAFILE/sysmgmtdata.260.819384687	      4000		    0 NO

If we try to reduce the size from 4Gb to 3Gb, it will warn and upon user confirmation drop all repository data

-bash-4.1$ oclumon manage -repos changerepossize 3000
Warning: Entire data in Cluster Health Monitor repository will be deleted.Do you want to continue(Yes/No)?
Yes
The Cluster Health Monitor repository was successfully resized.The new retention is 199620 seconds.

Tracefiles for db are stored under DIAG_HOME/_mgmtdb/-MGMTDB/trace. Alert log for instance can be found at this location. Since file name start with -MGMTDB*, we need to use ./ to access files. e.g

[oracle@oradbdev02]~/diag/rdbms/_mgmtdb/-MGMTDB/trace% vi -MGMTDB_mmon_7670.trc
VIM - Vi IMproved 7.2 (2008 Aug 9, compiled Feb 17 2012 10:23:31)
Unknown option argument: "-MGMTDB_mmon_7670.trc"
More info with: "vim -h"
[oracle@oradbdev02]~/diag/rdbms/_mgmtdb/-MGMTDB/trace% vi ./-MGMTDB_mmon_7670.trc

Sample output from a 3 node RAC setup

[oracle@oradbdev02]~% oclumon dumpnodeview -allnodes

----------------------------------------
Node: oradbdev02 Clock: '13-07-23 07.19.00' SerialNo:1707 
----------------------------------------

SYSTEM:
#pcpus: 4 #vcpus: 4 cpuht: N chipname: Dual-Core cpu: 5.15 cpuq: 1 physmemfree: 469504 physmemtotal: 7928104 mcache: 5196464 swapfree: 8191992 swaptotal: 8191992 hugepagetotal: 0 hugepagefree: 0 hugepagesize: 2048 ior: 0 iow: 51 ios: 9 swpin: 0 swpout: 0 pgin: 134 pgout: 140 netr: 223.768 netw: 176.523 procs: 461 rtprocs: 25 #fds: 24704 #sysfdlimit: 779448 #disks: 6 #nics: 3 nicErrors: 0

TOP CONSUMERS:
topcpu: 'oraagent.bin(7090) 2.59' topprivmem: 'java(7247) 149464' topshm: 'ora_mman_snowy1(7783) 380608' topfd: 'ocssd.bin(6249) 273' topthread: 'crsd.bin(6969) 42' 

----------------------------------------
Node: oradbdev03 Clock: '13-07-23 07.19.02' SerialNo:47 
----------------------------------------

SYSTEM:
#pcpus: 4 #vcpus: 4 cpuht: N chipname: Dual-Core cpu: 3.65 cpuq: 2 physmemfree: 1924468 physmemtotal: 7928104 mcache: 4529232 swapfree: 8191992 swaptotal: 8191992 hugepagetotal: 0 hugepagefree: 0 hugepagesize: 2048 ior: 1 iow: 83 ios: 17 swpin: 0 swpout: 0 pgin: 45 pgout: 55 netr: 67.086 netw: 55.042 procs: 373 rtprocs: 22 #fds: 21280 #sysfdlimit: 779448 #disks: 6 #nics: 3 nicErrors: 0

TOP CONSUMERS:
topcpu: 'osysmond.bin(19281) 1.99' topprivmem: 'ocssd.bin(19323) 83528' topshm: 'ora_mman_snowy2(20306) 261508' topfd: 'ocssd.bin(19323) 249' topthread: 'crsd.bin(19617) 40' 

----------------------------------------
Node: oradbdev04 Clock: '13-07-23 07.18.58' SerialNo:1520 
----------------------------------------

SYSTEM:
#pcpus: 4 #vcpus: 4 cpuht: N chipname: Dual-Core cpu: 3.15 cpuq: 1 physmemfree: 1982828 physmemtotal: 7928104 mcache: 4390440 swapfree: 8191992 swaptotal: 8191992 hugepagetotal: 0 hugepagefree: 0 hugepagesize: 2048 ior: 0 iow: 25 ios: 4 swpin: 0 swpout: 0 pgin: 57 pgout: 27 netr: 81.148 netw: 41.761 procs: 355 rtprocs: 24 #fds: 20064 #sysfdlimit: 779450 #disks: 6 #nics: 3 nicErrors: 0

TOP CONSUMERS:
topcpu: 'ocssd.bin(6745) 2.00' topprivmem: 'ocssd.bin(6745) 83408' topshm: 'ora_mman_snowy3(8168) 381768' topfd: 'ocssd.bin(6745) 247' topthread: 'crsd.bin(7202) 40'

You can learn more about oclumon usage by referring to Oclumon Command Reference

I faced error in my setup where I was getting ora-28000 error while using oclumon command. I tried unlocking account and it didn’t succeed.

oclumon dumpnodeview

dumpnodeview: Node name not given. Querying for the local host
CRS-9118-Grid Infrastructure Management Repository connection error 
 ORA-28000: the account is locked

SQL> alter user chm account unlock;

User altered.

dumpnodeview: Node name not given. Querying for the local host
CRS-9118-Grid Infrastructure Management Repository connection error 
 ORA-01017: invalid username/password; logon denied

This issue occurred as post configuration tasks had failed during GI installation. Solution is to run mgmtca from grid home which fixed the issue by unlocking and setting password for users. Wallet was configured for oclumon to be able to access the repository without hard coding password.

[main] [ 2013-07-23 05:32:41.619 UTC ] [Mgmtca.main:102]  Running mgmtca
[main] [ 2013-07-23 05:32:41.651 UTC ] [Mgmtca.execute:192]  Adding internal user1
[main] [ 2013-07-23 05:32:41.653 UTC ] [Mgmtca.execute:194]  Adding internal user2
[main] [ 2013-07-23 05:32:42.028 UTC ] [Mgmtca.isMgmtdbOnCurrentNode:306]  Management DB is running on blr-devdb-003local node is blr-devdb-003
[main] [ 2013-07-23 05:32:42.074 UTC ] [MgmtWallet.createWallet:54]  Wallet created
[main] [ 2013-07-23 05:32:42.084 UTC ] [Mgmtca.execute:213]  MGMTDB Wallet created
[main] [ 2013-07-23 05:32:42.085 UTC ] [Mgmtca.execute:214]  Adding user/passwd to MGMTDB Wallet
[main] [ 2013-07-23 05:32:42.210 UTC ] [MgmtWallet.terminate:122]  Wallet closed
[main] [ 2013-07-23 05:32:42.211 UTC ] [Mgmtca.execute:227]  Unlocking user and setting password in database
[main] [ 2013-07-23 05:32:42.211 UTC ] [Mgmtjdbc.connect:66]  Connection String=jdbc:oracle:oci:@(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/home/oragrid/bin/oracle)(ARGV0=oracle-MGMTDB)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))')(ENVS='ORACLE_HOME=/home/oragrid,ORACLE_SID=-MGMTDB')))
[main] [ 2013-07-23 05:32:42.823 UTC ] [Mgmtjdbc.connect:72]  Connection Established

These are two internal users being referred above

select username,account_status from dba_users where username like 'CH%';

USERNAME		       ACCOUNT_STATUS
------------------------------ --------------------------------
CHM			       OPEN
CHA			       OPEN
23 July, 2013 by 12c 4

12c: Exporting Database Views as Tables

Starting Oracle Database 12c, you can export view to be imported as a table. There is no need to individually export each table, Data Pump will dump a table with the same columns as the view and with row data fetched from the view.
It also exports objects dependent on the view, such as grants and constraints. To use this view,we need to use parameter VIEWS_AS_TABLES.

Let’s see this feature in action using a example.

We have created a view on emp,dept table to show employee details along with manager name

create view emp_view as select emp.EMPNO, emp.ENAME , emp.JOB,mgr.ename MGRNAME, emp.HIREDATE,emp.SAL ,emp.COMM,dept.DNAME DEPTNAME FROM
EMP emp,DEPT dept,EMP mgr
where emp.deptno=dept.deptno 
and mgr.empno(+)=emp.mgr order by 1;

Create a datapump directory to store datapump dumpfile

create directory dpdir as '/home/oracle/datapump';

Now we take datapump export and specify view name in views_as_tables

$ expdp system views_as_tables=scott.emp_view directory=dpdir dumpfile=emp_view.dmp logfile=emp_view_exp.log

Export: Release 12.1.0.1.0 - Production on Mon Jul 22 12:05:26 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
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
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** views_as_tables=scott.emp_view directory=dpdir dumpfile=emp_view.dmp logfile=emp_view_exp.log 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS method: 16 KB
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported "SCOTT"."EMP_VIEW"                          8.781 KB      14 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /home/oracle/datapump/emp_view.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jul 22 12:05:52 2013 elapsed 0 00:00:20

We can see that this has exported 14 rows. To see if it actually works, we will import it but in a different schema (remap_schema does the trick here)

$impdp system remap_schema=scott:amitbans directory=dpdir dumpfile=emp_view.dmp logfile=emp_view_imp.log

Import: Release 12.1.0.1.0 - Production on Mon Jul 22 12:36:33 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
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
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** remap_schema=scott:amitbans directory=dpdir dumpfile=emp_view.dmp logfile=emp_view_imp.log 
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "AMITBANS"."EMP_VIEW"                       8.781 KB      14 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Mon Jul 22 12:36:48 2013 elapsed 0 00:00:09

Let’s verify the data

SQL> show user
USER is "AMITBANS"
SQL> select * from emp_view;

     EMPNO ENAME      JOB	MGRNAME    HIREDATE	    SAL       COMM DEPTNAME
---------- ---------- --------- ---------- --------- ---------- ---------- --------------
      7369 SMITH      CLERK	FORD	   17-DEC-80	    800 	   RESEARCH
      7499 ALLEN      SALESMAN	BLAKE	   20-FEB-81	   1600        300 SALES
      7521 WARD       SALESMAN	BLAKE	   22-FEB-81	   1250        500 SALES
      7566 JONES      MANAGER	KING	   02-APR-81	   2975 	   RESEARCH
      7654 MARTIN     SALESMAN	BLAKE	   28-SEP-81	   1250       1400 SALES
      7698 BLAKE      MANAGER	KING	   01-MAY-81	   2850 	   SALES
      7782 CLARK      MANAGER	KING	   09-JUN-81	   2450 	   ACCOUNTING
      7788 SCOTT      ANALYST	JONES	   19-APR-87	   3000 	   RESEARCH
      7839 KING       PRESIDENT 	   17-NOV-81	   5000 	   ACCOUNTING
      7844 TURNER     SALESMAN	BLAKE	   08-SEP-81	   1500 	 0 SALES
      7876 ADAMS      CLERK	SCOTT	   23-MAY-87	   1100 	   RESEARCH
      7900 JAMES      CLERK	BLAKE	   03-DEC-81	    950 	   SALES
      7902 FORD       ANALYST	JONES	   03-DEC-81	   3000 	   RESEARCH
      7934 MILLER     CLERK	CLARK	   23-JAN-82	   1300 	   ACCOUNTING

14 rows selected.

We can see from dictionary that this is now imported as a table and not view

SQL> select * from tab;

TNAME			       TABTYPE	CLUSTERID
------------------------------ ------- ----------
EMP_VIEW		       TABLE

There are few restrictions for using this feature

-The view must exist and it must be a relational view with only scalar, non-LOB columns.
-VIEWS_AS_TABLES parameter cannot be used with the TRANSPORTABLE=ALWAYS parameter.

Reference

http://docs.oracle.com/cd/E16655_01/server.121/e17639/dp_export.htm#BEHDIADG 

22 July, 2013 by 12c 1

12c: Sqlplus Displays Last Login Time For Non – Sys Users

12c database has introduced a pretty nifty Security feature which allows you to check last login time for non-sys user. e.g If I connect to scott user as below, it displays that I last logged in at Mon Jul 22 2013 09:06:07 +00:00. Time is displayed in local format (UTC in this case)

[oracle@oradbdev01]~% sqlplus scott/oracle

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 22 09:14:25 2013

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

Last Successful login time: Mon Jul 22 2013 09:06:07 +00:00

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

If you wish to disable this feature, you can use -nologintime option

 [oracle@oradbdev01]~% sqlplus -nologintime scott/oracle

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 22 09:16:37 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

Using connect on sqlplus will not display the last login time but will change the last login time counter

22 July, 2013 by 12c 1

12c:Limiting PGA with pga_aggregate_limit

This is a interesting feature for Oracle DBA’s. Till now there has been no way of limiting total PGA being used by oracle instance. _pga_max_size (hidden parameter) could be used to limit per process memory. SGA_TARGET used to be hard limit for Oracle SGA but PGA_AGGREGATE_TARGET only controlled allocation of PGA memory to each process via oracle algorithm. (I know some folks who thought pga_aggregate_target is hard limit, but that is incorrect)

Due to this many times we end up in a situation when we get paged for High CPU /Low memory utilization and found that high swapping was being caused by excessive PGA usage. Since kswapd (linux) is busy writing to swap, you will also see high CPU utilization.

At this moment, your memory might look like below

$free -m
 total used free shared buffers cached
Mem: 16057 15953 103 0 4 209
-/+ buffers/cache: 15739 317
Swap: 12287 5359 6928

PGA_AGGREGATE_LIMIT is supposed to put a hard limit on the PGA Memory usage. But unlike SGA_TARGET, this doesn’t mean that processes will reduce the memory consumption. Instead this will lead to process/session failure.

As per Oracle docs oracle will terminate sessions in following order

-Calls for sessions that are consuming the most untunable PGA memory are aborted.

– If PGA memory usage is still over the PGA_AGGREGATE_LIMIT, then the sessions and processes that are consuming the most untunable PGA memory are terminated.

Note: I don’t see any difference in above conditions 🙂

PGA_AGGREGATE_LIMIT will be set to higher of following values

– 2 GB (default value)
– 200% of the PGA_AGGREGATE_TARGET value
– 3 MB times the value of the PROCESSES parameter.
– Less then 1.2 X RAM – SGA
To simulate this, I did following test using big_table (Code from Asktom)

create table big_table tablespace data_4m
as
select rownum id,
 OWNER, OBJECT_NAME, SUBOBJECT_NAME,
 OBJECT_ID, DATA_OBJECT_ID,
 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
 TIMESTAMP, STATUS, TEMPORARY,
 GENERATED, SECONDARY
 from all_objects a
 where 1=0
/

alter table big_table nologging;
--Load the table
declare
 l_cnt number;
 l_rows number := &1;
begin
 insert /*+ append */
 into big_table
 select rownum,
 OWNER, OBJECT_NAME, SUBOBJECT_NAME,
 OBJECT_ID, DATA_OBJECT_ID,
 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
 TIMESTAMP, STATUS, TEMPORARY,
 GENERATED, SECONDARY
 from all_objects a
 where rownum <= &1;
l_cnt := sql%rowcount;
commit;
while (l_cnt < l_rows)
 loop
 insert /*+ APPEND */ into big_table
 select rownum+l_cnt,
 OWNER, OBJECT_NAME, SUBOBJECT_NAME,
 OBJECT_ID, DATA_OBJECT_ID,
 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
 TIMESTAMP, STATUS, TEMPORARY,
 GENERATED, SECONDARY
 from big_table
 where rownum <= l_rows-l_cnt;
 l_cnt := l_cnt + sql%rowcount;
 commit;
 end loop;
end;
/ 
--gather stats
exec dbms_stats.gather_table_stats ( ownname => 'AMIT',tabname => 'BIG_TABLE' )

Let’s check the size of table

select owner,table_name,num_rows from dba_tables where table_name='BIG_TABLE';

OWNER		     TABLE_NAME 	    NUM_ROWS
-------------------- -------------------- ----------
AMIT          	     BIG_TABLE		   110000000

PGA_AGGREGATE_LIMIT is set to 2G (default value). This can’t be lowered as 2G is least value allowed for it

SQL> alter system set pga_aggregate_limit=1g;
alter system set pga_aggregate_limit=1g
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00093: pga_aggregate_limit must be between 2048M and 100000G

Before proceeding with this test PGA_AGGREGATE_TARGET was bumped to 30g so that processes can get more PGA memory

Created a shell file with following code

cat 1.sh
modname=$1
sqlplus -s amit/amit <<EOF
set pages 1000 lines 100
set autot traceonly
exec dbms_application_info.set_module(module_name=>'${modname}',action_name=>'')
 select * from big_table t1,big_table t2 where t1.object_id =t2.object_id;
exit
EOF

This code is executed via for loop with 20 sessions

for ((i=1;i<=20;i++))
do
nohup sh 1.sh PGA_LIMIT${i} &
done

In separate session ran following queries

set pages 1000 lines 200
col event for a30
col module for a30
select s.sid,s.serial#,p.spid,p.pga_used_mem/1024/1024 PGA_MB,s.status,s.event,s.module from v$session s ,v$process p where s.paddr=p.addr and s.module like 'PGA_LIMIT%' and wait_class<>'Idle' order by module
/
select sum(pga_used_mem)/1024/1024 pga_used_mb from v$process;

Output of the query was like this

SQL> @pga

       SID    SERIAL# SPID			   PGA_MB STATUS   EVENT			  MODULE
---------- ---------- ------------------------ ---------- -------- ------------------------------ ------------------------------
       262	  243 20941		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT1
       390	   77 20957		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT10
       154	   11 20938		       130.816335 ACTIVE   direct path write temp	  PGA_LIMIT11
	35	   77 20921		       130.944379 ACTIVE   direct path write temp	  PGA_LIMIT12
       266	   35 20906		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT13
       141	   17 20917		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT14
       263	   59 20945		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT15
	25	   55 20943		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT16
       135	  119 20942		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT17
       383	  217 20923		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT18
       371	  391 20937		       130.816335 ACTIVE   direct path write temp	  PGA_LIMIT19
       382	  163 20955		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT2
	29	   35 20954		       130.816335 ACTIVE   direct path write temp	  PGA_LIMIT20
       144	  103 20947		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT3
       136	   15 20952		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT4
	38	    5 20926		       130.882039 ACTIVE   direct path write temp	  PGA_LIMIT5
       257	  139 20902		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT6
       260	   73 20939		       130.816335 ACTIVE   direct path write temp	  PGA_LIMIT7
       381	   13 20951		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT8
	23	  101 20915		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT9

20 rows selected.

Elapsed: 00:00:00.00

PGA_USED_MB
-----------
 2684.47882

Note that this is more then 2G limit and we still didn’t see failures. Reason is that we have set pga_aggregate_target to 30G . Checking parameter value for pga_aggregate_limit still shows 2G but it seems it has risen to higher value (since pga_aggregate_target is 30G now). At this moment, I reduced the pga_aggregate_target to 500m. Immediately some of the db sessions were automatically cleared. Excerpt from alert log

ALTER SYSTEM SET pga_aggregate_target=500M SCOPE=BOTH;
2013-07-07 11:27:34.015000 +00:00
Errors in file /home/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_20923.trc  (incident=12476):
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
Errors in file /home/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_20939.trc  (incident=12433):
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

Checking V$Session confirms same

      SID    SERIAL# SPID			   PGA_MB STATUS   EVENT			  MODULE
---------- ---------- ------------------------ ---------- -------- ------------------------------ ------------------------------
       262	  243 20941		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT1
       390	   77 20957		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT10
       266	   35 20906		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT13
       141	   17 20917		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT14
	25	   55 20943		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT16
       135	  119 20942		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT17
       382	  163 20955		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT2
       144	  103 20947		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT3
       136	   15 20952		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT4
       257	  139 20902		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT6
       381	   13 20951		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT8
	23	  101 20915		       130.878675 ACTIVE   direct path write temp	  PGA_LIMIT9

12 rows selected.

PGA_USED_MB
-----------
 1637.30552

Since limit is set to 2G, it has cleared sessions automatically. login_time is not considered for killing session as we can find PGA_LIMIT7, 11,12 missing from the remaining sessions.

 

7 July, 2013 by 12c 1

12c:Specifying lock timeout for finish_redef_table

Oracle 12c database has introduced enhancement to DBMS_REDEFINITION for specifying a a lock timeout (in seconds) which will allow FINISH_REDEF_TABLE to acquire an exclusive lock for swapping the source and interim tables.If timeout expires, then operation exits. This will help to   avoid cancellation by user or indefinite wait. This does not put our session in queue which will be shown by below example

1. Let’s test this on EMP table. I have created table in my schema and copied records.

CREATE TABLE "AMITBANS"."EMP"
   (	"EMPNO" NUMBER(4,0),
	"ENAME" VARCHAR2(10),
	"JOB" VARCHAR2(9),
	"MGR" NUMBER(4,0),
	"HIREDATE" DATE,
	"SAL" NUMBER(7,2),
	"COMM" NUMBER(7,2),
	"DEPTNO" NUMBER(2,0),
	 CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX) tablespace users;

2. Lets hash partition the table on empno. Note that I have excluded primary key constraint/index as we will copy it using dbms_redefinition

 CREATE TABLE "AMITBANS"."INT_EMP"
   (	"EMPNO" NUMBER(4,0),
	"ENAME" VARCHAR2(10),
	"JOB" VARCHAR2(9),
	"MGR" NUMBER(4,0),
	"HIREDATE" DATE,
	"SAL" NUMBER(7,2),
	"COMM" NUMBER(7,2),
	"DEPTNO" NUMBER(2,0)) tablespace users
  partition by hash(empno) 
  partitions 4;

3. Lets check if dbms_redefinition will work on this table

set serverout on
exec  DBMS_REDEFINITION.CAN_REDEF_TABLE('AMITBANS','EMP',DBMS_REDEFINITION.CONS_USE_PK);

There are no errors, so we are safe to proceed.

4. Next Start the redefinition

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(UNAME =>'AMITBANS', ORIG_TABLE=>'EMP',INT_TABLE=>'INT_EMP',OPTIONS_FLAG=>dbms_redefinition.cons_use_pk);
END;
/

5. Copy dependent objects. (Automatically create any triggers, indexes, materialized view logs, grants, and constraints on AMITBANS.INT_EMP.)

DECLARE
l_num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(UNAME =>'AMITBANS', ORIG_TABLE=>'EMP',INT_TABLE=>'INT_EMP',COPY_INDEXES=> DBMS_REDEFINITION.CONS_ORIG_PARAMS,
   COPY_TRIGGERS=>TRUE,COPY_CONSTRAINTS=> TRUE, COPY_PRIVILEGES=>TRUE, IGNORE_ERRORS =>TRUE,NUM_ERRORS=> l_num_errors,COPY_STATISTICS=>TRUE);
END;
/

We need to check if there were any errors while copying constraints. Use below query

select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;

6. We can sync any new inserts by issuing following command.

 BEGIN 
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE(UNAME =>'AMITBANS', ORIG_TABLE=>'EMP',INT_TABLE=>'INT_EMP');
END;
/

7. Let’s do finish redefinition, but before that we will do two insert in emp table from different session without committing transaction.
We will do it in following order to show that finish_redef_table needs to have no active dml on table.

session 1: Insert record 1

insert into emp values(8000,'HENRY','ANALYST',7698,sysdate,1500,null,10);

Session 2: Issue finish_redef_table to swap tables

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(UNAME =>'AMITBANS', ORIG_TABLE=>'EMP',INT_TABLE=>'INT_EMP',dml_lock_timeout=>300);
END;
/

Session 3: Open one more session and do insert

insert into emp values(8001,'MICHAEL','ANALYST',7698,sysdate,1500,null,10);

If you now commit transaction in session 1, redefinition (session 2) will  wait  (to acquire exclusive lock) for session 3 to commit .This shows that it is not in a queue and needs all active transactions to finish. When we commit session 3,  finish_redef_table succeeds.

3 July, 2013 by 12c 0

Openfiler on Virtualbox and 12c Oracle Flex ASM

Last week Oracle released 12c database and Oracle blogosphere is bustling with lot of people posting details about new versions and setup.
I too decided to take plunge and setup my own RAC cluster. I had 4 machines with me but no shared storage 🙁
Long back I had done one installation using openfiler (Followed Jeff Hunter’s article on OTN) but in that case we installed openfiler software on base machine. Finally I decided to try installing it on Virtualbox.
I checked openfiler website and found out that they provide templates for VM which meant that installation on VM was supported. (Anyways this is my test setup 🙂 )

This was 64 bit machine, So I downloaded 64 bit rpm Virtualbox software.

When you try to install it, this will fail with following error

rpm -i VirtualBox-4.2-4.2.14_86644_el6-1.x86_64.rpm 
warning: VirtualBox-4.2-4.2.14_86644_el6-1.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 98ab5139: NOKEY
error: Failed dependencies:
	libSDL-1.2.so.0()(64bit) is needed by VirtualBox-4.2-4.2.14_86644_el6-1.x86_64

Solution : Install SDL package . You can either install SDL through yum or install same rpm using yum and it will find dependent SDL package and install it for you.

#yum install SDL
# rpm -i VirtualBox-4.2-4.2.14_86644_el6-1.x86_64.rpm 
warning: VirtualBox-4.2-4.2.14_86644_el6-1.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 98ab5139: NOKEY

Creating group 'vboxusers'. VM users must be member of that group!

No precompiled module for this kernel found -- trying to build one. Messages
emitted during module compilation will be logged to /var/log/vbox-install.log.

WARNING: Deprecated config file /etc/modprobe.conf, all config files belong into /etc/modprobe.d/.
Stopping VirtualBox kernel modules [  OK  ]
Recompiling VirtualBox kernel modules [  OK  ]
Starting VirtualBox kernel modules [  OK  ]

Add oracle user to vboxusers to allow oracle user to manage VM

usermod -G vboxusers oracle

Once done, you can download openfiler software from http://www.openfiler.com/community/download/. You can get direct link for 64 bit binary  at Sourceforge.net

Let’s Build Virtual Machine for our setup.

1. Start virtualbox GUI by issuing virtualbox on command line
2. Click on New VM and choose OS (Linux) and Version (Red Hat 64 bit, change it based on your OS)
3. Allocate memory to Machine. I opted for 3G
4. Create a virtual drive of 30G and use .VDI as format. Even though usage is less then 8G, openfiler install fails if you create 8G disk.
5. Once done, click finish and click on settings.
6. Choose Network as eth0 and Bridged adapter. We are using single adapter here
7. Modify Boot order and remove floppy.Keep hard disk as first option
8. In System storage, You can add additional hard disk (Say 100G) which will be used to setup ASM devices

You can use screenshots from oracle-base 12c install article . Note that we are using single adapter and Bridged adapter.

8. When you start VM, it will ask you to choose start-up disk. Choose your openfiler.iso image
9. Press enter on boot screen and then click next

openfiler1
10. Choose install language
11. Next choose hard disk which will be used for installing software. Choose 30G disk which we allocated earlier. Uncheck the second disk

openfiler2
12. Next screen is network configuration. This is most important screen. I used static IP configuration , click edit for eth0 and put all required information i.e IP,subnet,gateway.Also add hostname and DNS information here.

openfiler3

13. Next select timezone and set root password. Once done, you will get success message and reboot will be done. If everything is successful, you can find your setup at

https://hostname:446/ (note its https and not http)

If this doesn’t work then look at your ip settings and ensure that your ip is pingable from outside. More troubleshooting for GUI can be done by restarting openfiler and httpd service. If it gives error, you can troubleshoot further

service openfiler restart
service httpd restart

You can configure openfiler ASM volumes by following  Jeff Hunter article
In case you are on RHEL6, udev rules mentioned in above link will not work. Frits Hoogland article will be of help here (http://fritshoogland.wordpress.com/2012/07/23/using-udev-on-rhel-6-ol-6-to-change-disk-permissions-for-asm/)

You should be ready with ASM storage and can proceed with RAC install. There is not much difference in 12c install except that we have new feature called Oracle ASM Flex. I am documenting screenshots for same here

1. Choose Standard Cluster here. If you choose Flex cluster, it would force you to use GNS as option can’t be unchecked.

flexcluster1

2.  Choose Advanced Install

3. When choosing network interface, select ASM and private for private interface

flexcluster2

4. On screen 10, choose Oracle Flex ASM

flexcluster3

 

I did two Flex cluster setup with 3 node RAC and 2 node RAC and it seems to work at both places. Let’s see Flex cluster in action

You can verify if your ASM is enabled to use Flex mode using below command

[oracle@oradbdev02]~% asmcmd showclustermode
ASM cluster : Flex mode enabled

crsctl command can be used to set Flex mode later. Below command will show configuration

oracle@oradbdev02]~% srvctl config asm
ASM home: /home/oragrid
Password file: +VDISK/orapwASM
ASM listener: LISTENER
ASM instance count: 3
Cluster ASM listener: ASMNET1LSNR_ASM

[oracle@oradbdev02]~% srvctl status asm -detail
ASM is running on oradbdev02,oradbdev03,oradbdev04
ASM is enabled.

Lets reduce the ASM to run only on 2 nodes.This will stop ASM on one node

oracle@oradbdev02]~% srvctl modify asm -count 2

[oracle@oradbdev02]~% srvctl status asm -detail 
ASM is running on oradbdev02,oradbdev03
ASM is enabled.

There is no ASM on node oradbdev04 but db is still running

[oracle@oradbdev04]~% ps -ef|grep pmon
oracle    3949     1  0 10:27 ?        00:00:00 ora_pmon_snowy3
oracle   18728     1  0 07:59 ?        00:00:01 apx_pmon_+APX3

If you now try to start ASM on 3rd node, it will give error

[oracle@oradbdev02]~% srvctl config asm
ASM home: /home/oragrid
Password file: +VDISK/orapwASM
ASM listener: LISTENER
ASM instance count: 2
Cluster ASM listener: ASMNET1LSNR_ASM

[oracle@oradbdev02]~% srvctl start asm -n oradbdev04
PRCR-1013 : Failed to start resource ora.asm
PRCR-1064 : Failed to start resource ora.asm on node oradbdev04
CRS-2552: There are no available instances of resource 'ora.asm' to start.

Lets make it count back to 3 and ASM will start now

[oracle@oradbdev02]~% srvctl modify asm -count 3
[oracle@oradbdev02]~% srvctl start asm -n oradbdev04
[oracle@oradbdev02]~% srvctl status asm -detail     
ASM is running on oradbdev02,oradbdev03,oradbdev04
ASM is enabled.

Now need to explore some other new feature

2 July, 2013 by 12c 0

Disclaimer!!

This blog reflect our own views and do not necessarily represent the views of our current or previous employers.

The contents of this blog are from our experience, you may use at your own risk, however you are strongly advised to cross reference with Product documentation and test before deploying to production environments.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 188 other subscribers