Oct-13
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; 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/
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
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=