Oct-13

Leave a comment Go to comments

31-Oct

To configure Oracle flashback, configure the FRA.


SQL> alter system set db_recovery_file_dest_size=3G scope=both;

System altered.

SQL> alter system set db_recovery_file_dest='/n01/oradata1/flash/oraclds/' scope=both;

System altered.

SQL> alter system set db_flashback_retention_target=5000 scope=both ;

System altered.

SQL> shutdown immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount ;

ORACLE instance started.

Total System Global Area 3206836224 bytes

Fixed Size                  2232640 bytes

Variable Size            1325403840 bytes

Database Buffers         1778384896 bytes

Redo Buffers              100814848 bytes

Database mounted.

SQL> alter database flashback on ;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database recover managed standby database disconnect from session through last switchover;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON

------------------

YES

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ ONLY WITH APPLY

Flashback primary, recover the data and roll forward – Impact on standby

In primary


SQL> select current_scn from v$database;

CURRENT_SCN

-----------

7730123

SQL> conn thomas/thomas

Connected.

SQL> select count(*) from t;

COUNT(*)

----------

1

SQL> truncate table t;

Table truncated.

Changes got propagated to active dataguard.


SQL>  select open_mode from v$database;

OPEN_MODE

--------------------

READ ONLY WITH APPLY

SQL> select count(*) from thomas.t;

COUNT(*)

----------

0

Now, from Primary flashback the database to previous SCN to get the truncated data.

 SQL> conn / as sysdba

Connected.

SQL>  shutdown immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount ;

ORACLE instance started.

Total System Global Area 2355015680 bytes

Fixed Size                  2230592 bytes

Variable Size            1157629632 bytes

Database Buffers         1107296256 bytes

Redo Buffers               87859200 bytes

Database mounted.

SQL> flashback database to scn 7730123;

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> select count(*) from thomas.t;

COUNT(*)

----------

1

After recovering the data from the primary, shutdown/mount the database and roll forward to complete recovery.


SQL> shutdown immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount ;

ORACLE instance started.

Total System Global Area 2355015680 bytes

Fixed Size                  2230592 bytes

Variable Size            1157629632 bytes

Database Buffers         1107296256 bytes

Redo Buffers               87859200 bytes

Database mounted.

SQL> recover database;

Media recovery complete.

SQL> alter database open ;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ WRITE

Re-start the standby database and place in active recovery mode


SQL> shutdown  immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 3206836224 bytes

Fixed Size                  2232640 bytes

Variable Size            1325403840 bytes

Database Buffers         1778384896 bytes

Redo Buffers              100814848 bytes

Database mounted.

Database opened.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION THROUGH LAST SWITCHOVER;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ ONLY WITH APPLY

Insert a record in the primary


SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ WRITE

SQL> insert into thomas.t values(1,'s','s',1);

1 row created.

SQL> commit ;

Commit complete.

SQL> select count(*) from thomas.t ;

COUNT(*)

----------

1

Check in the active dataguard for propogation.


SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ ONLY WITH APPLY

SQL> select count(*) from thomas.t;

COUNT(*)

----------

1

So, you can flashback database in the primary and replay back without affecting the standby database.

 

Flashback standby database, recover data and bring back to recovery mode

Get the current SCN from the standby database;


SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ ONLY WITH APPLY

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

7731079

SQL> select count(*) from thomas.t;

COUNT(*)

----------

1

The record is existing in THOMAS.T table and let us truncate that table in  primary.


SQL> conn thomas/thomas

Connected.

SQL> select count(*) from t;

COUNT(*)

----------

1

SQL> truncate table t;

Table truncated.

SQL> select count(*) from t;

COUNT(*)

----------

0

Changes are propagated to the standby.


SQL> conn thomas/thomas

Connected.

SQL> select count(*) from t;

COUNT(*)

----------

0

Now flashback database in standby and recover the data.


SQL> conn / as sysdba

Connected.

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ ONLY WITH APPLY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel ;

Database altered.

SQL> shutdown immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount ;

ORACLE instance started.

Total System Global Area 3206836224 bytes

Fixed Size                  2232640 bytes

Variable Size            1325403840 bytes

Database Buffers         1778384896 bytes

Redo Buffers              100814848 bytes

Database mounted.

SQL> flashback database to scn 7731079;

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> conn thomas/thomas

Connected.

SQL> select count(*) from t;

COUNT(*)

----------

1

I got my record back – now bring back the standby database to active data guard.


SQL> conn / as sysdba

Connected.

SQL>  shutdown immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount ;

ORACLE instance started.

Total System Global Area 3206836224 bytes

Fixed Size                  2232640 bytes

Variable Size            1325403840 bytes

Database Buffers         1778384896 bytes

Redo Buffers              100814848 bytes

Database mounted.

SQL> SQL> alter database open ;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION THROUGH LAST SWITCHOVER;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ ONLY WITH APPLY

SQL> select count(*) from thomas.t;

COUNT(*)

----------

0

Now the standby is sync with the primary. Inserted two records to the T table in primary.

 SQL> conn thomas/thomas

Connected.

SQL> insert into t values(2,'d','g',5);

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

And the standby is in sync now.


SQL> conn thomas/thomas

Connected.

SQL> select count(*) from t;

COUNT(*)

----------

2

24-Oct

New parameter in 12c,  HEAT_MAP  ON/OFF by default OFF.  When turn on, Oracle tracks modifications to all segments and invoke a defined ILM (Information Lifecyle Management) policy.  This parameter also enables ADO ( Automatic Data Optimization) feature.


SQL> show parameter heat_map

NAME                                 TYPE                             VALUE

------------------------------------ -------------------------------- ------------------------------

heat_map                             string                           OFF

SQL> alter system set heat_map=ON scope=both ;

System altered.

SQL> show parameter heat_map

NAME                                 TYPE                             VALUE

------------------------------------ -------------------------------- ------------------------------

heat_map                             string                           ON

In DBA_TABLES – ACTIVITY_TRACKING column is specifies the heat map is enabled for a table.

Disable adaptive cursor sharing :              _optim_peek_user_binds = FALSE

23-Oct

A cursor in a session can OPEN or closed.  A closed cursor may be existing in the session cache, based on the number of executions by that session.


SQL> select KGLLKADR,KGLLKSQLID,KGLLKCTP ,KGLLKEXC from x$kgllk where  USER_NAME='THOMAS';

 

KGLLKADR         KGLLKSQLID    KGLLKCTP                                                           KGLLKEXC

---------------- ------------- ---------------------------------------------------------------- ----------

00000001124156E8 8tfvmdy25w4m3 OPEN                                                                      0

00000000E80E2318 7czxxkt1sha75 OPEN                                                                      0

0000000112415A78 6zg6u2fdt0c93 OPEN                                                                      0

0000000112413EA8 bv2wa2ch2tn6x OPEN                                                                      0

00000000E80DB828 4vs91dcv7u1p6 OPEN-RECURSIVE                                                            0

00000000E80DB928 4vs91dcv7u1p6 OPEN                                                                      1

00000000E80E2418 34v8q2gg5a49y OPEN                                                                      0

0000000112411EB8 2qarsrbyqkau5 OPEN                                                                      0

00000001124155E8 7wtvhjqprfdx0 OPEN                                                                      0

000000011240E110 8619rrqzkur32 SESSION CURSOR CACHED                                                     0

0000000112415978 8619rrqzkur32 OPEN                                                                      4

0000000112415B78 281f962r4zaum OPEN                                                                      0

0000000112414B30 cwnvyjq1ybj4k OPEN                                                                      0

0000000112414E30 cwnvyjq1ybj4k OPEN                                                                      0

14 rows selected.

The column KGLLKCTP tell us the status of the cursor, If it is OPEN, the cursor is still OPEN.  It may be cached or not cached.  A closed cursor can be cached, and if it is cached, the value of KGLLKCTP will be “SESSION CURSOR CACHED”.

 

In Oracle 12c, new mandatory process: LREG – Listener Registration

http://docs.oracle.com/cd/E16655_01/server.121/e17633/process.htm#CHDJJAEH

New parameter:  PROCESSOR_GROUP_NAME. CPU and memory can be grouped in OS level and assign to the database, so that Oracle can be restricted to use a certain CPUs/memory in OS level.  In solaris CPU grouping is known as Resource Pools and in Linux – Control Groups (CGroups).

http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn10323.htm

Renaming a PDB

  • Open PDB in restricted mode
  • Re-name the PDB
  • Start the PDB

SQL> conn / as sysdba

Connected.

SQL> select name,OPEN_MODE from v$pdbs;

NAME                           OPEN_MODE

------------------------------ ----------

PDB$SEED                       READ ONLY

SALESPDB                       READ WRITE

 

SQL> alter pluggable database SALESPDB close immediate ;

Pluggable database altered.

SQL> alter pluggable database SALESPDB open restricted;

Pluggable database altered.

SQL> alter pluggable database SALESPDB rename global_name to salesdb;

alter pluggable database SALESPDB rename global_name to salesdb                             *

ERROR at line 1:

ORA-65046: operation not allowed from outside a pluggable database

SQL> alter session set container=SALESPDB;

Session altered.

SQL> alter pluggable database SALESPDB rename global_name to salesdb;

Pluggable database altered.

SQL> alter pluggable database close immediate ;

Pluggable database altered.

SQL>  alter pluggable database open ;

Pluggable database altered.

SQL> conn / as sysdba

Connected.

SQL> select name,OPEN_MODE from v$pdbs;

NAME                           OPEN_MODE

------------------------------ ----------

PDB$SEED                       READ ONLY

SALESDB                        READ WRITE

Clone a PDB from a PDB in the same container


SQL> conn / as sysdba

Connected.

SQL> alter pluggable database SALESDB close immediate ;

Pluggable database altered.

SQL> alter pluggable database SALESDB open read only ;

Pluggable database altered.

SQL> alter session set db_create_file_dest='/n01/oradata1/12cDB/sadbcopy';

Session altered.

SQL> create pluggable database sadbcopy from SALESDB;

Pluggable database created.

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE

------------------------------ ----------

PDB$SEED                       READ ONLY

SALESDB                        READ ONLY

SADBCOPY                       MOUNTED

SQL> alter pluggable database SALESDB close immediate ;

Pluggable database altered.

SQL>  alter pluggable database SALESDB open ;

Pluggable database altered.

SQL> alter pluggable database SADBCOPY open ;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE

------------------------------ ----------

PDB$SEED                       READ ONLY

SALESDB                        READ WRITE

SADBCOPY                       READ WRITE

Move files:  oracle 12c allows move the files even when the database is UP and running – including the SYSTEM tablespace files.


SQL> conn / as sysdba

Connected.

NAME

----------------------------------------------------------------------------------------------------------------------------------

/n01/oradata1/12cDB/cdb/undotbs01.dbf

/n01/oradata1/12cDB/sadbcopy/12CDB/E9661DC294367714E0434488340A52DF/datafile/o1_mf_system_96h5lqq5_.dbf

/n01/oradata1/12cDB/sadbcopy/12CDB/E9661DC294367714E0434488340A52DF/datafile/o1_mf_sysaux_96h5lqwf_.dbf

/n01/oradata1/12cDB/sadbcopy/12CDB/E9661DC294367714E0434488340A52DF/datafile/o1_mf_users_96h5lqp9_.dbf

/n01/oradata1/12cDB/sadbcopy/12CDB/E9661DC294367714E0434488340A52DF/datafile/o1_mf_usertbs_96h5lqof_.dbf

/n01/oradata1/12cDB/sadbcopy/12CDB/E9661DC294367714E0434488340A52DF/datafile/o1_mf_sales_96h5lrj9_.dbf

 

6 rows selected.

SQL> alter pluggable database SADBCOPY open ;

 

Pluggable database altered.

 

SQL> alter pluggable database sadbcopy move datafile

2  '/n01/oradata1/12cDB/sadbcopy/12CDB/E9661DC294367714E0434488340A52DF/datafile/o1_mf_system_96h5lqq5_.dbf' to '/n01/oradata1/12cDB/sadbcopy/system01.dbf';

alter pluggable database sadbcopy move datafile

*

ERROR at line 1:

ORA-65046: operation not allowed from outside a pluggable database

 

 

SQL> alter session set container=SADBCOPY;

 

Session altered.

 

SQL> alter pluggable database sadbcopy move datafile

2  '/n01/oradata1/12cDB/sadbcopy/12CDB/E9661DC294367714E0434488340A52DF/datafile/o1_mf_system_96h5lqq5_.dbf' to '/n01/oradata1/12cDB/sadbcopy/system01.dbf';

Pluggable database altered.

SQL> alter pluggable database sadbcopy move datafile

2  '/n01/oradata1/12cDB/sadbcopy/12CDB/E9661DC294367714E0434488340A52DF/datafile/o1_mf_sysaux_96h5lqwf_.dbf' to '/n01/oradata1/12cDB/sadbcopy/sysaux01.dbf';

Pluggable database altered.

 

SQL> alter pluggable database sadbcopy move datafile

'/n01/oradata1/12cDB/sadbcopy/12CDB/E9661DC294367714E0434488340A52DF/datafile/o1_mf_users_96h5lqp9_.dbf' to '/n01/oradata1/12cDB/sadbcopy/users01.dbf';

Pluggable database altered.

SQL> alter pluggable database sadbcopy move datafile

2  '/n01/oradata1/12cDB/sadbcopy/12CDB/E9661DC294367714E0434488340A52DF/datafile/o1_mf_usertbs_96h5lqof_.dbf' to '/n01/oradata1/12cDB/sadbcopy/usertbs01.dbf';

Pluggable database altered.

SQL> alter pluggable database sadbcopy move datafile

2  '/n01/oradata1/12cDB/sadbcopy/12CDB/E9661DC294367714E0434488340A52DF/datafile/o1_mf_sales_96h5lrj9_.dbf' to '/n01/oradata1/12cDB/sadbcopy/sales01.dbf';

Pluggable database altered.

SQL>  select file_name from dba_data_files;

FILE_NAME

---------------------------------------------

/n01/oradata1/12cDB/sadbcopy/sysaux01.dbf

/n01/oradata1/12cDB/sadbcopy/users01.dbf

/n01/oradata1/12cDB/sadbcopy/usertbs01.dbf

/n01/oradata1/12cDB/sadbcopy/sales01.dbf

/n01/oradata1/12cDB/sadbcopy/system01.dbf

22-Oct

In 10046 trace output, the cursor close section give us four different types of closes.  In the below output the close type is 3.


EXEC #139715936652664:c=0,e=77,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2035254952,tim=1382432982882237

FETCH #139715936652664:c=0,e=41,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,plh=2035254952,tim=1382432982882288

CLOSE #139715936652664:c=0,e=3,dep=1,type=3,tim=1382432982882313
  • type 0 : Cursor is closed and not added to the session cache
  • type 1 : Cursor is closed but added to the session cache without flushing out an existing cursor
  • type 2 : Cursor is closed but added to the session cache after flushing out an existing cursor in cache
  • type 3 : Cursor is already existing in the cache

After executing an SQL 3 times – the cursor will be cached in the session.  First 2 executions, the ‘close type’ will be 0, third time will be cached with type 1 and further executions type value will be 3 – already cached.

First execution – type 0


PARSING IN CURSOR #140509816707992 len=17 dep=0 uid=53 oct=3 lid=53 tim=1382434154192039 hv=3207421026 ad='11e5a20b8' sqlid='8619rrqzkur32'

select * from abc

END OF STMT

PARSE #140509816707992:c=2000,e=2512,p=0,cr=27,cu=0,mis=1,r=0,dep=0,og=1,plh=1033171814,tim=1382434154192038

EXEC #140509816707992:c=0,e=16,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1033171814,tim=1382434154192092

WAIT #140509816707992: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1382434154192126

WAIT #140509816707992: nam='Disk file operations I/O' ela= 20 FileOperation=2 fileno=5 filetype=2 obj#=64820 tim=1382434154192203

WAIT #140509816707992: nam='db file sequential read' ela= 661 file#=5 block#=72978 blocks=1 obj#=64820 tim=1382434154192884

FETCH #140509816707992:c=999,e=792,p=1,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=1033171814,tim=1382434154192945

WAIT #140509816707992: nam='SQL*Net message from client' ela= 174 driver id=1650815232 #bytes=1 p3=0 obj#=64820 tim=1382434154193590

CLOSE #140509816707992:c=0,e=5,dep=0,type=0,tim=1382434154193617

Second execution – type 0


PARSING IN CURSOR #140509816707992 len=17 dep=0 uid=53 oct=3 lid=53 tim=1382434154193688 hv=3207421026 ad='11e5a20b8' sqlid='8619rrqzkur32'

select * from abc

END OF STMT

PARSE #140509816707992:c=0,e=47,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1033171814,tim=1382434154193688

EXEC #140509816707992:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1033171814,tim=1382434154193836

WAIT #140509816707992: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=64820 tim=1382434154193863

FETCH #140509816707992:c=0,e=21,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=1033171814,tim=1382434154193924

WAIT #140509816707992: nam='SQL*Net message from client' ela= 60 driver id=1650815232 #bytes=1 p3=0 obj#=64820 tim=1382434154194009

WAIT #140509816707992: nam='SQL*Net message from client' ela= 133 driver id=1650815232 #bytes=1 p3=0 obj#=64820 tim=1382434154194218

CLOSE #140509816707992:c=0,e=3,dep=0,type=0,tim=1382434154194244

Third execution – type 1 – caching the cursor in the session cache


PARSING IN CURSOR #140509816707992 len=17 dep=0 uid=53 oct=3 lid=53 tim=1382434154194284 hv=3207421026 ad='11e5a20b8' sqlid='8619rrqzkur32'

select * from abc

END OF STMT

PARSE #140509816707992:c=0,e=18,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1033171814,tim=1382434154194283

STAT #140509816707992 id=1 cnt=1 pid=0 pos=1 obj=64820 op='TABLE ACCESS FULL ABC (cr=6 pr=0 pw=0 time=14 us cost=3 size=3 card=1)'

WAIT #140509816707992: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=64820 tim=1382434154194527

WAIT #140509816707992: nam='SQL*Net message from client' ela= 108 driver id=1650815232 #bytes=1 p3=0 obj#=64820 tim=1382434154194644

CLOSE #140509816707992:c=0,e=6,dep=0,type=1,tim=1382434154194671

Fourth and subsequent executions – type 3 – already cached.


PARSE #140509816707992:c=0,e=9,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1033171814,tim=1382434154194703

EXEC #140509816707992:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1033171814,tim=1382434154194808

WAIT #140509816707992: nam='SQL*Net message to client' ela= 0 driver id=1650815232 #bytes=1 p3=0 obj#=64820 tim=1382434154194835

FETCH #140509816707992:c=0,e=18,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=1033171814,tim=1382434154194868

WAIT #140509816707992: nam='SQL*Net message from client' ela= 80 driver id=1650815232 #bytes=1 p3=0 obj#=64820 tim=1382434154194970

FETCH #140509816707992:c=0,e=8,p=0,cr=2,cu=0,mis=0,r=0,dep=0,og=1,plh=1033171814,tim=1382434154195000

WAIT #140509816707992: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=64820 tim=1382434154195023

WAIT #140509816707992: nam='SQL*Net message from client' ela= 115 driver id=1650815232 #bytes=1 p3=0 obj#=64820 tim=1382434154195154

CLOSE #140509816707992:c=0,e=5,dep=0,type=3,tim=1382434154195181

http://hoopercharles.wordpress.com/2011/07/21/session_cached_cursors-possibly-interesting-details/

SGA resize operations


column initial_size format 999999999999999

column target_size format 999999999999999

column final_size format 999999999999999

set linesize 200

select to_char(end_time, 'dd-Mon-yyyy hh24:mi') end, oper_type, initial_size,

target_size, final_size from V$SGA_RESIZE_OPS

where component='shared pool'

order by end;

List of containers in a CDB


SELECT NAME, CON_ID, DBID, CON_UID FROM V$CONTAINERS ORDER BY CON_ID;

PDB open modes


SELECT NAME, OPEN_MODE, RESTRICTED FROM V$PDBS;

Oracle 12c multi-tenant architecture

http://docs.oracle.com/cd/E16655_01/server.121/e17633/cdblogic.htm

To set and list  a container


Alter session set container=<container name>;

Show con_name;



&nbsp;

Creating a local user -  in  a PDB



SQL> show con_name

CON_NAME

------------------------------

SALESPDB

SQL> create user thomas identified by thomas;

User created.

SQL> grant connect,resource to thomas;

Grant succeeded.

SQL> conn thomas/thomas

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

You must connect directly to the PDB, as the new user is local to a PDB


SQL> conn thomas/thomas@emea1:1521/salespdb

Connected.

To open a PDB


SQL> SELECT NAME, OPEN_MODE, RESTRICTED FROM V$PDBS;

NAME                           OPEN_MODE  RES

------------------------------ ---------- ---

PDB$SEED                       READ ONLY  NO

SALESPDB                       MOUNTED

SQL> alter pluggable database SALESPDB open;

Pluggable database altered.

V$session : CON_ID   -> session container ID

17-Oct

To see the last CPU patch applied – from v$registry$history


set linesize 200

col COMMENTS for a20

select ACTION_TIME,VERSION,BUNDLE_SERIES ,COMMENTS from  registry$history ;

X$KCBKPFS - [K]ernal [C]ache [B]uffer chec[K]point management [P]re[F]etch [S]tats

Some block prefecth stats


select STATISTIC#,NAME,value from v$sysstat where name like '%prefetch%'

select * from X$KCBKPFS where PREFETCH_BLOCKS > 0;

 

16-Oct

_cursor_features_enabled is the parameter to enable the pacth : 11724916.  This patch is applicable both in 11.1.0.7 and 11.2.0.2.  The objective of this patch is to fix extensive Mutex S Contention due to large number of SQL version count.

In 11.1.0.7

_cursor_features_enabled=18

events= "106001 trace name context forever, level 500"

 

In 11.2.0.2

_cursor_features_enabled=1026  => 26 or 1026 – need to check

events= "106001 trace name context forever, level 1024"

See the MOS Doc: 10187168.8 for other details

https://blogs.oracle.com/UPGRADE/entry/upgrade_and_an_interesting_surprise

15-Oct

Impact of granule size, sub-pools on various memory structures

http://www.pythian.com/blog/why-is-oracle-ignoring-my-memory-parameters/

14-Oct

The hidden parameter _kghdsidx_count defines the number of sub-pools in the shared pool, can be up to 7 sub-pools.

You can see the number of sub-pools using


select count(kghluidx) num_subpools

from x$kghlu;

The number of shared pool sub-pools are decided by

  • The number of CPUs in the server
  • Size of the SGA allocated

12-Oct

To get the heartbeat miscount settings in CRS cluster – between the Hub nodes

$ crsctl get css misscount

To get the miscount between the leaf node and a Hub node which the leaf node is connected which defaults to 30 seconds

$ crsctl get css leafmisscount

Both miscount configuration can be set manually

$ crsctl set css misscount 25

$ crsctl set css leafmisscount 25

You need to set the values in Hub node NOT from the leaf node as the leaf node is not connected to OCR and Voting disk

In 12cR1, CRS can be run in two modes

  • Standard – 11gR2 way
  • Flex Cluster – 12cR1 new feature
    • A cluster node will either a Hub node or leaf node
    • A hub node will be connected the storage, including OCR and Voting disk – exactly behave like a 11gR2 cluster node
    • A leaf node will be connected to a Hub node and no direct interaction with storage including OCR or voting disk
    • GNS must be running to enable the flux cluster
    • Known as Hub - and – spoke architecture
    • There can be upto 64 hub nodes

You can get current mode of a cluster

$ crsctl get cluster mode status

To get the Hub size of the cluster

$ crsctl get cluster hubsize

List all node roles

$ crsctl get node role status –all

$ crsctl get node role status

To check GNS is running or not

$ crsctl status gns

To enable flux cluster

$ crsctl set cluster mode flex

To configure GNS

$ crsctl add gns –vip <VIP name> | IP address

To start GNS

$ crsctl start gns

To set a node as hub or leaf node

$ crsctl set node role { leaf | hub }

http://docs.oracle.com/cd/E16655_01/rac.121/e17886/bigcluster.htm

To get the configuration of VIP from a Hub node

$ srvctl config vip –n <node anme>

To configure a VIP in a Hub node

$ srvctl add vip –n <node name> -A <IP>/<net mask> /<Network interface> -k <Network number>

Some excellent notes on GNS

http://martincarstenbach.wordpress.com/2011/11/17/simplified-gns-setup-for-rac-11-2-0-2-and-newer/

http://martincarstenbach.wordpress.com/2009/10/02/build-your-own-11-2-rac-system-part-ii-dns-dhcp-for-gns/

To check GNS before CRS  I nstallation – precheck

$ runcluvfy comp gns –precrsinst –domain <sub domain> -vip <ip addresss> -verbose  -n <nodename>

Post installation check

$ cluvfy comp gns –postcrsinst -verbose

To enable GNS, post CRS installaltion

$ srvctl config gns –a

To check GNC configuration and  list dynamically allocated IPs

$srvctl config gns -l

04-Oct

CLOB performance issues

http://jhdba.wordpress.com/2013/10/03/large-clobs-and-attention-to-detail-needed-while-reading-oracle-support-notes/

5 new Oracle 12c new features for developers..

http://blog.iadvise.eu/2013/10/03/5-neat-little-features-of-the-12c-database-to-remember/

OOW-2013 slides!

http://connormcdonald.wordpress.com/2013/10/03/openworld-slides/

12c in-database archiving issue

http://oracleinaction.com/12c-performance-issue-database-archiving/

All about oracle 12c security

http://www.oracle.com/us/products/database/security/overview/index.html

03-Oct

Oracle 12c container and seed database creation


CREATE DATABASE "12cdb"

USER SYS IDENTIFIED BY oracle

USER SYSTEM IDENTIFIED BY oracle

LOGFILE GROUP 1 ('/n01/oradata1/12cDB/cdb/redo01a.log','/n01/oradata1/12cDB/cdb/redo01b.log')

SIZE 100M BLOCKSIZE 512,

GROUP 2 ('/n01/oradata1/12cDB/cdb/redo02a.log','/n01/oradata1/12cDB/cdb/redo02b.log')

SIZE 100M BLOCKSIZE 512,

GROUP 3 ('/n01/oradata1/12cDB/cdb/redo03a.log','/n01/oradata1/12cDB/cdb/redo03b.log')

SIZE 100M BLOCKSIZE 512

MAXLOGHISTORY 1

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 1024

CHARACTER SET AL32UTF8

NATIONAL CHARACTER SET AL16UTF16

EXTENT MANAGEMENT LOCAL

DATAFILE '/n01/oradata1/12cDB/cdb/system01.dbf'

SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

SYSAUX DATAFILE '/n01/oradata1/12cDB/cdb/sysaux01.dbf'

SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

DEFAULT TABLESPACE users

DATAFILE '/n01/oradata1/12cDB/cdb/userso1.dbf'

SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

DEFAULT TEMPORARY TABLESPACE tempts1

TEMPFILE '/n01/oradata1/12cDB/cdb/temp01.dbf'

SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED

UNDO TABLESPACE undotbs1

DATAFILE '/n01/oradata1/12cDB/cdb/undotbs01.dbf'

SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED

ENABLE PLUGGABLE DATABASE

SEED

FILE_NAME_CONVERT = ('/n01/oradata1/12cDB/cdb',

'/n01/oradata1/12cDB/pdbseed/')

SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED

SYSAUX DATAFILES SIZE 100M

USER_DATA TABLESPACE usertbs

DATAFILE '/n01/oradata1/12cDB/pdbseed/usertbs01.dbf'

SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Got below error while creating a pluggable database.


SQL> CREATE PLUGGABLE DATABASE salespdb

2    ADMIN USER salesadm IDENTIFIED BY salesadm

3    ROLES = (dba)

4    DEFAULT TABLESPACE sales

5      DATAFILE '/n01/oradata1/12cDB/salespdb/sales01.dbf' SIZE 250M AUTOEXTEND ON

6    FILE_NAME_CONVERT = ('/n01/oradata1/12cDB/pdbseed','/n01/oradata1/12cDB/salespdb')

7    STORAGE (MAXSIZE 2G)

8    PATH_PREFIX = '/n01/oradata1/12cDB/salespdb/';

CREATE PLUGGABLE DATABASE salespdb

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-00942: table or view does not exist

The problem was – I ran the catalog and catproc in a traditional way so that it wasn’t run in the PDB$SEED database.  So, I have to run the these scripts as below to execute both in CDB and all PDBs.


PERL5LIB=$ORACLE_HOME/rdbms/admin:$PERL5LIB; export PERL5LIB

perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS -d $ORACLE_HOME/rdbms/admin -b catalog_output catalog.sql

perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS -d $ORACLE_HOME/rdbms/admin -b catproc_output catproc.sql

perl $ORACLE_HOME/rdbms/admin/catcon.pl -u system/oracle -d $ORACLE_HOME/sqlplus/admin -b pupbld_output pupbld.sql

Again I stuck with another issue, my CATPROC session crashed with below error.  I felt, the reason was, there were less free memory and session got crashed.  I shutdown another running database to make room and it went very well.


No errors.

ERROR:

ORA-03114: not connected to ORACLE

CREATE OR REPLACE package body dbms_stats wrapped

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 1120

Session ID: 19 Serial number: 15

No errors.

After running above 3 scripts using the new way, I was able to create my first plubggable database!

01-Oct

Internal Notes on ASM

http://asmsupportguy.blogspot.in/

Oracle 12c FETCH FIRST

http://dspsd.blogspot.in/2013/09/top-selling-items-revisited-in-12c.html

Virtual status

http://jonathanlewis.wordpress.com/2013/09/27/virtual-stats/

Private redo and IMU

http://jonathanlewis.wordpress.com/oracle-core/oc-2-undo-and-redo/

runinstaller in silent mode


/n01/oradata1/software/database/runInstaller -silent -noconfig -responseFile /home/oracle/rfile -ignoreSysPrereqs

Below is the response file.


oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v12.1.0

oracle.install.option=INSTALL_DB_SWONLY

ORACLE_HOSTNAME=xxxxxxxxxxx

UNIX_GROUP_NAME=oinstall

INVENTORY_LOCATION=/u01/app/oraInventory

SELECTED_LANGUAGES=en

ORACLE_HOME=/u01/app/oracle/product/12.1/

ORACLE_BASE=/u01/app/oracle

oracle.install.db.InstallEdition=EE

oracle.install.db.DBA_GROUP=dba

oracle.install.db.OPER_GROUP=dba

oracle.install.db.BACKUPDBA_GROUP=dba

oracle.install.db.DGDBA_GROUP=dba

oracle.install.db.KMDBA_GROUP=dba

oracle.install.db.rac.configurationType=

oracle.install.db.CLUSTER_NODES=

oracle.install.db.isRACOneInstall=

oracle.install.db.racOneServiceName=

oracle.install.db.rac.serverpoolName=

oracle.install.db.rac.serverpoolCardinality=

oracle.install.db.config.starterdb.type=GENERAL_PURPOSE

oracle.install.db.config.starterdb.globalDBName=sourcetest

oracle.install.db.config.starterdb.SID=sourcetest

oracle.install.db.ConfigureAsContainerDB=

oracle.install.db.config.PDBName=

oracle.install.db.config.starterdb.characterSet=WE8MSWIN1252

oracle.install.db.config.starterdb.memoryOption=true

oracle.install.db.config.starterdb.memoryLimit=1024

oracle.install.db.config.starterdb.installExampleSchemas=false

oracle.install.db.config.starterdb.password.ALL=picasso

oracle.install.db.config.starterdb.password.SYS=picasso

oracle.install.db.config.starterdb.password.SYSTEM=

oracle.install.db.config.starterdb.password.DBSNMP=

oracle.install.db.config.starterdb.password.PDBADMIN=

oracle.install.db.config.starterdb.managementOption=

oracle.install.db.config.starterdb.omsHost=

oracle.install.db.config.starterdb.omsPort=

oracle.install.db.config.starterdb.emAdminUser=

oracle.install.db.config.starterdb.emAdminPassword=

oracle.install.db.config.starterdb.enableRecovery=false

oracle.install.db.config.starterdb.storageType=FILE_SYSTEM_STORAGE

oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=/u01/app/oracle/oradata

oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=

oracle.install.db.config.asm.diskGroup=

oracle.install.db.config.asm.ASMSNMPPassword=

MYORACLESUPPORT_USERNAME=

MYORACLESUPPORT_PASSWORD=

SECURITY_UPDATES_VIA_MYORACLESUPPORT=false

DECLINE_SECURITY_UPDATES=true

PROXY_HOST=

PROXY_PORT=

PROXY_USER=

PROXY_PWD=

PROXY_REALM=

COLLECTOR_SUPPORTHUB_URL=

oracle.installer.autoupdates.option=SKIP_UPDATES

oracle.installer.autoupdates.downloadUpdatesLoc=

AUTOUPDATES_MYORACLESUPPORT_USERNAME=

AUTOUPDATES_MYORACLESUPPORT_PASSWORD=
Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s

%d bloggers like this: