Microsoft Azure AZ-303 Study Guide

https://query.prod.cms.rt.microsoft.com/cms/api/am/binary/RE4HxvD 

Find IP Address subnets Range

https://www.ultratools.com/tools/netMaskResult?ipAddress=10.1.0.0%2F16  

Subnetting Tutorial 

https://www.subnetting.net/Tutorial.aspx 

Set Up Federation Trust Between Azure AD and Identity Cloud Service

https://docs.oracle.com/en/solutions/sso-peoplesoft-azure-ad/set-federation-trust-azure-ad-and-identity-cloud-service.html#GUID-8F8499CC-B748-4F6F-BAEA-3C4365448BAE 

Federating with Identity Providers 

https://docs.cloud.oracle.com/en-us/iaas/Content/Identity/Concepts/federation.htm

Azure Charts

https://azurecharts.com/

Microsoft Azure 104 Notes

https://ravikirans.com/az-104-azure-exam-study-guide/

Oracle Performance Tuning

How to Analyze the Performance History of a SQL Statement as Recorded in AWR Using Simple SQL (Doc ID 1580764.1)

http://www.nocoug.org/download/2008-08/a-tour-of-the-awr-tables.nocoug-Aug-21-2008.abercrombie.html

https://www.realdbamagic.com/script-finding-top-n-queries-user-awr/

https://blog.pythian.com/mining-the-awr-to-identify-performance-trends/

https://karlarao.wordpress.com/scripts-resources/

https://github.com/tanelpoder/tpt-oracle

https://blog.tanelpoder.com/posts/oracle-sql-monitoring-advanced-ash-usage-hacking-session/
https://blog.tanelpoder.com/2018/05/18/my-performance-troubleshooting-scripts-tpt-for-oracle-are-now-in-github-and-open-sourced/
https://github.com/tanelpoder/tpt-oracle
https://www.youtube.com/TanelPoder

EXTREME DETAILS ABOUT A SQL
https://mjsoracleblog.wordpress.com/2013/02/19/my-oracle_performance-github-repository/

https://github.com/khailey/ashmasters

http://bdrouvot.wordpress.com/real_time/ => script to get real time I/O

Oracle GoldenGate GGSCI Commands

https://docs.oracle.com/goldengate/1212/gg-winux/GWURF/ggsci_commands.htm#GWURF895

Database STARTUP_TIME History

set lines 1000
col STARTUP_TIME for a25
col platform for a40

select DBID, INSTANCE_NUMBER INST_NU,STARTUP_TIME,PARALLEL RAC, VERSION, DB_NAME,
INSTANCE_NAME,HOST_NAME ,LAST_ASH_SAMPLE_ID ASH_ID,PLATFORM_NAME PLATFORM
from dba_hist_database_instance order by STARTUP_TIME;

Golden Gate Lag Monitoring Script

http://sridharramireddy.blogspot.com/2014/09/script-to-monitor-goldengate-monitoring.html

Find Query Versions

SQL to find Query Versions based on a particular SQL_ID
=> you can remove the columns selected based upon your need.

Select inst_id, sql_id,address, hash_value, sql_text, version_count,loaded_versions,
executions,loads,invalidations
from gv$sqlarea
where version_count >= 1
and sql_id ='&Input_SQL_ID'
order by inst_id,version_count
/

Find historical data using SQL_ID

SET LINESIZE 120
SET PAGESIZE 1000
col snap_begin format a35
COL executions FOR 999,999,999
COL elapsed_time FOR 999,999,999,999
COL avg_ms FOR 9999999.99
COL min_ms FOR 9999999.99
COL max_ms FOR 9999999.99
SELECT
TO_CHAR(TRUNC(snapshot.begin_interval_time,'HH24'),'DD-MON-YYYY HH24:MI:SS') || ' EDT' SNAP_BEGIN,
sqlstat.instance_number,
SUM(sqlstat.executions_delta) executions,
MIN(sqlstat.elapsed_time_delta / sqlstat.executions_delta / 1000) min_ms,
MAX(sqlstat.elapsed_time_delta / sqlstat.executions_delta / 1000) max_ms,
SUM(sqlstat.elapsed_time_delta) / SUM(sqlstat.executions_delta) / 1000 avg_Ms
FROM
dba_hist_sqlstat sqlstat,
dba_hist_snapshot snapshot
WHERE
sqlstat.dbid = snapshot.dbid
AND sqlstat.instance_number = snapshot.instance_number
AND sqlstat.snap_id = snapshot.snap_id
AND sqlstat.sql_id = '&1'
AND snapshot.begin_interval_time >= SYSDATE-60
GROUP
BY TO_CHAR(TRUNC(snapshot.begin_interval_time,'HH24'),'DD-MON-YYYY HH24:MI:SS') || ' EDT',
sqlstat.instance_number
ORDER
BY snap_begin
/

SAMPLE OUTPUT

Enter value for 1: 6ajkhumm78nrp

SNAP_BEGIN                          INSTANCE_NUMBER   EXECUTIONS      MIN_MS      MAX_MS      AVG_MS
----------------------------------- --------------- ------------ ----------- ----------- -----------
01-JUL-2015 00:00:00 EDT                          1            2    16160.94    16569.28    16365.11
01-JUL-2015 00:00:00 EDT                          4            2    16530.52    96809.78    56670.15
01-JUL-2015 01:00:00 EDT                          1            2    16008.20    16273.29    16140.74
01-JUL-2015 01:00:00 EDT                          4            2    96150.86    96213.51    96182.18
01-JUL-2015 02:00:00 EDT                          1            2    16417.98    16606.18    16512.08
01-JUL-2015 02:00:00 EDT                          4            2    96991.15    97816.36    97403.75
01-JUL-2015 02:00:00 EDT                          5            2   121063.36   271762.59   196412.97

Troubleshooting 'log file sync'

Very Good Document on "log file sync" on My Oracle Support Site.
Troubleshooting: 'Log file sync' Waits (Doc ID 1376916.1)

http://www.pythian.com/blog/adaptive-log-file-sync-oracle-please-dont-do-that-again/

Oracle GoldenGate Limitations and Restrictions

Oracle GoldenGate supports two types of capture:

Classic Capture
Integrated Capture

Classic Capture
Oracle GoldenGate continues to support the existing Capture module, now referred to as Classic
Capture, which directly accesses the database redo logs looking for DML changes to capture for
distribution.
It is possible to capture from redo logs stored inside of ASM. Adjusting the read
size can improve Extract performance. In this mode, Extract can be integrated
with Oracle RMAN to manage log retention.


Excellent Post by another DBA - Credit goes to him.
http://sandeepnandhadba.blogspot.com/2014/12/oracle-golden-gate-12-bidirectional.html

Oracle ASM Commands

col PATH form a25
col GB_read for 99999
col GB_written for 9999

select a.PATH, a.mount_status, a.state, a.TOTAL_MB, a.FREE_MB, a.mount_date, a.bytes_read/1024/1024/1024 GB_read, 
a.BYTES_WRITTEN/1024/1024/1024 GB_written, dg.name AS DISK_GROUP
from v$asm_disk_stat a, V$ASM_DISKGROUP dg
WHERE dg.group_number = a.group_number
order by disk_group
/

PATH                      MOUNT_S STATE      TOTAL_MB    FREE_MB MOUNT_DAT GB_READ GB_WRITTEN DISK_GROUP
------------------------- ------- -------- ---------- ---------- --------- ------- ---------- ----------
ORCL:ASM252_VMAX0050F     CACHED  NORMAL       246246      23607 25-AUG-14       6         13 MY_DATA
ORCL:ASM252_VMAX00510     CACHED  NORMAL       246246      23616 25-AUG-14       6         13 MY_DATA
ORCL:ASM252_VMAX01C82     CACHED  NORMAL       246246      60811 25-AUG-14      39         11 MY_RECOVERY
ORCL:ASM252_VMAX01C83     CACHED  NORMAL       246246      60808 25-AUG-14      40         11 MY_RECOVERY

set pages 50000 lines 100 echo on head on flush on veri on feed on
col INSTANCE_NAME form a20
col SOFTWARE_VERSION form a20
col COMPATIBLE_VERSION form a20

select * from v$asm_client;

GROUP_NUMBER INSTANCE_NAME        DB_NAME  STATUS       SOFTWARE_VERSION     COMPATIBLE_VERSION
------------ -------------------- -------- ------------ -------------------- --------------------
           1 +ASM1                mylxtdb  CONNECTED    11.1.0.7.0           11.0.0.0.0
           2 +ASM1                mylxtdb  CONNECTED    11.1.0.7.0           11.0.0.0.0

select DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,PATH,TOTAL_MB from gv$asm_disk;

DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE    PATH                             TOTAL_MB
----------- ------- ------------ ------- -------- ------------------------------ ----------
          2 CACHED  MEMBER       ONLINE  NORMAL   ORCL:ASM252_VMAX0050F              246246
          3 CACHED  MEMBER       ONLINE  NORMAL   ORCL:ASM252_VMAX00510              246246
          0 CACHED  MEMBER       ONLINE  NORMAL   ORCL:ASM252_VMAX01C82              246246

SELECT SUBSTR(d.name,1,16) AS asmdisk,PATH, dg.name AS diskgroup 
FROM V$ASM_DISKGROUP dg, V$ASM_DISK d 
WHERE dg.group_number = d.group_number 
and dg.name in ('MY_DATA','MY_RECOVERY') order by dg.name ;

ASMDISK          PATH                                     DISKGROUP
---------------- ---------------------------------------- ---------------------
ASM252_VMAX01C83 ORCL:ASM252_VMAX01C83                    MY_DATA
ASM252_VMAX01F67 ORCL:ASM252_VMAX01F67                    MY_DATA
ASM252_VMAX01C85 ORCL:ASM252_VMAX01C85                    MY_RECOVERY
ASM252_VMAX01C84 ORCL:ASM252_VMAX01C84                    MY_RECOVERY

Identify the Disks you want to add and Disks you want to remove by using "oracleasm listdisks" as oracle user on the server.
The below command would add the disks you want to add and remove the older (existing disks) and do the re-balance. You can set
the rebalance power from 1 to 11. 11 would use more resources and would be the fastest way to rebalance.
NOTE: When adding Disk(s), you have to prefix the device name with "ORCL:"

alter diskgroup MY_DATA
 add disk
  'ORCL:ASM176_VMAX02049',
  'ORCL:ASM176_VMAX0204A'
 drop disk
  'ASM252_VMAX01F67',
  'ASM252_VMAX01C82',
rebalance power 11 ;

Check the rebalance progress using the below script.

select sysdate, GROUP_NUMBER, OPERATION, STATE, POWER, ACTUAL, SOFAR, EST_WORK, EST_RATE, EST_MINUTES
from v$asm_operation;

How to find and kill the DBMS jobs running

select job_name, session_id, running_instance, elapsed_time, cpu_used
from dba_scheduler_running_jobs;

JOB_NAME SESSION_ID RUNNING_INSTANCE
------------------- ---------- ----------------
JOB_DEL_PROJECTS 67 4

Now you can stop the job using

exec DBMS_SCHEDULER.stop_JOB (job_name => 'JOB_DEL_PROJECTS');

or you can kill the SID

11g RAC: Killing user sessions from different instance

You can be logged onto instance 1 and can kill user sessions for all other instances in the RAC cluster.

ALTER SYSTEM KILL SESSION 'SID, SERIAL#,[@INST_ID]'

The below statement will generate the script which you can run on any instance to kill session from all the instances.

select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''';' from gv$session where osuser='SCOTT';

Active Session History Queries

Oracle DBA scripts: Active Session History Queries
-- TOP events
select event,
sum(wait_time +time_waited) ttl_wait_time
from v$active_session_history
where sample_time between sysdate - 60/2880 and sysdate
group by event
order by 2

-- Top sessions
select sesion.sid,
sesion.username,
sum(ash.wait_time + ash.time_waited)/1000000/60 ttl_wait_time_in_minutes
from v$active_session_history ash, v$session sesion
where sample_time between sysdate - 60/2880 and sysdate
and ash.session_id = sesion.sid
group by sesion.sid, sesion.username
order by 3 desc

--Top queries
SELECT active_session_history.user_id,
dba_users.username,
sqlarea.sql_text,
SUM(active_session_history.wait_time +
active_session_history.time_waited)/1000000 ttl_wait_time_in_seconds
FROM v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE
AND active_session_history.sql_id = sqlarea.sql_id
AND active_session_history.user_id = dba_users.user_id
and dba_users.username <>'SYS'
GROUP BY active_session_history.user_id,sqlarea.sql_text, dba_users.username
ORDER BY 4 DESC

-- Top segments
SELECT dba_objects.object_name,
dba_objects.object_type,
active_session_history.event,
SUM(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
FROM v$active_session_history active_session_history,
dba_objects
WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE
AND active_session_history.current_obj# = dba_objects.object_id
GROUP BY dba_objects.object_name, dba_objects.object_type, active_session_history.event
ORDER BY 4 DESC

-- Most IO
SELECT sql_id, COUNT(*)
FROM gv$active_session_history ash, gv$event_name evt
WHERE ash.sample_time > SYSDATE - 1/24
AND ash.session_state = 'WAITING'
AND ash.event_id = evt.event_id
AND evt.wait_class = 'User I/O'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;

SELECT * FROM TABLE(dbms_xplan.display_cursor('&SQL_ID));

-- Top 10 CPU consumers in last 60 minutes
select * from
(
select session_id, session_serial#, count(*)
from v$active_session_history
where session_state= 'ON CPU' and
sample_time > sysdate - interval '60' minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum <= 10; -- Top 10 waiting sessions in last 60 minutes select * from ( select session_id, session_serial#,count(*) from v$active_session_history where session_state='WAITING' and sample_time > sysdate - interval '60' minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum <= 10; -- Find session detail of top sid by passing sid select serial#, username, osuser, machine, program, resource_consumer_group, client_info from v$session where sid=&sid; -- Find different sql_ids of queries executed in above top session by-passing sid select distinct sql_id, session_serial# from v$active_session_history where sample_time > sysdate - interval '60' minute
and session_id=&sid

--Find full sqltext (CLOB) of above sql
select sql_fulltext from v$sql where sql_id='&sql_id'

--find session wait history of above found top sessionselect * from v$session_wait_history where sid=&sid

--find all wait events for above top session
select event, total_waits, time_waited/100/60 time_waited_minutes,
average_wait*10 aw_ms, max_wait/100 max_wait_seconds
from v$session_event
where sid=&sid
order by 5 desc

--session statistics for above particular top session :
select s.sid,s.username,st.name,se.value
from v$session s, v$sesstat se, v$statname st
where s.sid=se.SID and se.STATISTIC#=st.STATISTIC#
--and st.name ='CPU used by this session'
--and s.username='&USERNAME'
and s.sid='&SID'
order by s.sid,se.value desc

Auto Task Status

EXEC DBMS_AUTO_TASK_ADMIN.disable;

and if you query, you might see something like this.

col client_name for a50
col status for a10

select client_name,status FROM dba_autotask_client ;
CLIENT_NAME STATUS
-------------------------------------------------- ----------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED


the above may not give you the clear picture.

You the below query to get the autotask_status of these jobs

col window_name for a20
col window_next_time for a25
select window_name, to_char(cast(window_next_time as date),'DD/MM/YYYY HH24:MI:SS') window_next_time,
window_active, autotask_status, optimizer_stats, segment_advisor, sql_tune_advisor,
health_monitor from DBA_AUTOTASK_WINDOW_CLIENTS ;

You can also individually disable them.

begin
dbms_auto_task_admin.disable( client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
end;
/

Who's accessing a particular table

While trying to alter any table or index, you may see error similar to below

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

You can find out who's accessing the object and may kill that session to proceed with altering the object.

SELECT OBJECT_ID, SESSION_ID, inst_id FROM GV$LOCKED_OBJECT
WHERE OBJECT_ID=(select object_id FROM dba_objects where object_name='EMPLOYEE' and object_type='TABLE' and owner='SCOTT') ;

and the below statement will generate the commands to kill those sesssions.

select 'alter system kill session '||''''||a.SESSION_ID||','||b.serial#||''';' FROM GV$LOCKED_OBJECT a, gv$session b
where a.session_id=b.sid AND OBJECT_ID=(select object_id FROM dba_objects where object_name='EMPLOYEE' and object_type='TABLE' and owner='SCOTT') ;

and the below statement will generate the command to kill sessions on another instance as well.

select 'alter system kill session '||''''||a.SESSION_ID||','||b.serial#||',@'||b.inst_id||''';' FROM GV$LOCKED_OBJECT a, gv$session b
where a.session_id=b.sid AND OBJECT_ID=(select object_id FROM dba_objects where object_name='EMPLOYEE' and object_type='TABLE' and owner='SCOTT')
/

select
o.object_type,
o.object_name,
DECODE(v.locked_mode,
1, 'no lock',
2, 'row share (SS)',
3, 'row exclusive (SX)',
4, 'shared table (S)',
5, 'shared row exclusive (SSX)',
6, 'exclusive (X)') lock_mode,
v.oracle_username,
v.os_user_name,
v.session_id
from
all_objects o,
gv$locked_object v
where
o.object_id = v.object_id;


SELECT l.inst_id,SUBSTR(L.ORACLE_USERNAME,1,8) ORA_USER, SUBSTR(L.SESSION_ID,1,3) SID,
S.serial#,
SUBSTR(O.OWNER||'.'||O.OBJECT_NAME,1,40) OBJECT, P.SPID OS_PID,
DECODE(L.LOCKED_MODE, 0,'NONE',
1,'NULL',
2,'ROW SHARE',
3,'ROW EXCLUSIVE',
4,'SHARE',
5,'SHARE ROW EXCLUSIVE',
6,'EXCLUSIVE',
NULL) LOCK_MODE
FROM sys.GV_$LOCKED_OBJECT L, DBA_OBJECTS O, sys.GV_$SESSION S, sys.GV_$PROCESS P
WHERE L.OBJECT_ID = O.OBJECT_ID
and l.inst_id = s.inst_id
AND L.SESSION_ID = S.SID
and s.inst_id = p.inst_id
AND S.PADDR = P.ADDR(+)
order by l.inst_id

Query the session_longops

select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done, to_char(sysdate + TIME_REMAINING/3600/24,'MM-DD-YYYY, HH24:MI:SS') end_at from v$session_longops
where totalwork > sofar AND username like 'SCOTT%'
/

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

The ‘ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired’ error can probably be avoided on a running system by setting the session ddl_lock_timeout e.g. ‘alter session set ddl_lock_timeout=5;’ will tell Oracle to retry for 5 seconds.

How to get explain plan and predicate information while running the SQL Statement

SQL> set autotrace traceonly explain
SQL> select sysdate from dual ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------

SQL>

Find files for a date and time and then copy to a directory

-rw-r--r-- 1 mys1idb oragrid 0 Sep 19 21:23 mys1id5_ora_19242.trc
-rw-r--r-- 1 mys1idb oragrid 0 Sep 19 21:23 mys1id5_ora_19281.trc
-rw-r--r-- 1 mys1idb oragrid 0 Sep 19 21:23 mys1id5_ora_19317.trc
-rw-r--r-- 1 mys1idb oragrid 0 Sep 19 21:23 mys1id5_ora_19325.trc
-rw-r--r-- 1 mys1idb oragrid 0 Sep 19 21:23 mys1id5_ora_19333.trc
-rw-r--r-- 1 mys1idb oragrid 0 Sep 19 21:24 mys1id5_ora_19386.trc

for i in `ls -latR | grep "Sep 19 21" | awk '{print $9}'`; do cp -pr $i /tmp/trace; done

Trick to generate SQL from an Excel File

="INSERT INTO Table (ID, Name) VALUES (" & C2 & ", '" & D2 & "')"

How to resize redo logs for RAC with ASM

Find out the current redo log size

select b.group#,b.thread#,a.member, b.bytes/1024/1024 from v$logfile a, v$log b where a.GROUP#=b.GROUP# order by 1 ;
    GROUP#    THREAD# MEMBER                                                       B.BYTES/1024/1024
---------- ---------- ------------------------------------------------------------ -----------------
         1          1 +RECOVERY/myracdb/onlinelog/group_1.9390.825874743                       1024
         1          1 +RECOVERY/myracdb/onlinelog/group_1.11031.825874749                      1024
         2          1 +RECOVERY/myracdb/onlinelog/group_2.323.825874755                        1024
         2          1 +RECOVERY/myracdb/onlinelog/group_2.11085.825874761                      1024
         3          2 +RECOVERY/myracdb/onlinelog/group_3.324.825874767                        1024
         3          2 +RECOVERY/myracdb/onlinelog/group_3.10010.825874773                      1024
         4          2 +RECOVERY/myracdb/onlinelog/group_4.8913.825874779                       1024
         4          2 +RECOVERY/myracdb/onlinelog/group_4.5641.825874785                       1024

Now create some temporary groups

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 11 ( '+RECOVERY', '+RECOVERY') SIZE 1024M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 12 ( '+RECOVERY', '+RECOVERY') SIZE 1024M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 13 ( '+RECOVERY', '+RECOVERY') SIZE 1024M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 14 ( '+RECOVERY', '+RECOVERY') SIZE 1024M;

Now you have to keep switch them until you can drop the group 1 thru 4, you can put that in a script and
keep running until you have dropped group 1 thru 4

alter system switch logfile ;
alter system switch logfile ;
alter database drop logfile group 1 ;
alter database drop logfile group 2 ;
alter database drop logfile group 3 ;
alter database drop logfile group 4 ;

Now repeat the process by creating the Group 1 thur 4 with desired size (say 2G each)

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 ( '+RECOVERY', '+RECOVERY') SIZE 2048M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 2 ( '+RECOVERY', '+RECOVERY') SIZE 2048M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3 ( '+RECOVERY', '+RECOVERY') SIZE 2048M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 4 ( '+RECOVERY', '+RECOVERY') SIZE 2048M;

Now you can drop the temporary groups 11 thru 14

alter system switch logfile ;
alter system switch logfile ;
alter database drop logfile group 11 ;
alter database drop logfile group 12 ;
alter database drop logfile group 13 ;
alter database drop logfile group 14 ;

Redo Threads
Each online redo log has a thread number and a sequence number. The thread number is mainly relevant in RAC databases where
there can be multiple threads; one for each instance. The thread number is not necessarily the same as the instance number.
For single instance databases there is only one redo log thread at any time.

Redo Log Groups
A redo thread consists of two or more redo log groups.

Each redo log group contains one or more physical redo log files known as members. Multiple members are configured to provide
protection against media failure (mirroring). All members within a redo log group should be identical at any time.

Each redo log group has a status. Possible status values include UNUSED, CURRENT, ACTIVE and INACTIVE. Initially redo log
groups are UNUSED. Only one redo log group can be CURRENT at any time. Following a log switch, redo log group continues to be
ACTIVE until a checkpoint has completed. Thereafter the redo log group becomes INACTIVE until it is reused by the LGWR background process.

Log Switches
Log switches occur when the online redo log becomes full. Alternatively log switches can be triggered externally by commands such as:

ALTER SYSTEM SWITCH LOGFILE;

When a log switch occurs, the sequence number is incremented and redo continues to be written to the next
file in the sequence. If archive logging is enabled, then following a low switch the completed online redo log will be copied to the archive
log destination(s) either by the ARCH background process or the LNSn background process depending on the configuration.

ETL Vs ELT - Explanation

Very Nice explanation of the terms ETL and ELT...

http://blog.performancearchitects.com/wp/2013/06/13/etl-vs-elt-whats-the-difference/

- credit goes to the original creator of the content.

Invisible Indexes and its impact on Foreign Keys

Invisible Indexes on Foreign Keys can still be used by Oracle to prevent locking and performance
related issues when delete/update operations are performed on the parent records.

for more information read a very nice article by Richard Foote.
http://richardfoote.wordpress.com/category/invisible-indexes/

ORA-02297: cannot disable constraint ( ........ ) - dependencies exist

SQL> alter table scott.employee disable constraint employee_pk ;
ORA-02297: cannot disable constraint (SCOTT.EMPLOYEE_PK) - dependencies exist

Problem
Disable constraint command fails as the table is parent table and it has foreign
key that are dependent on this constraint.

Fix
There are two things we can do here.
1)Find foreign key constraints on the table and disable those foreign key constraints and then disable this table constraint.

Following query will check dependent table and the dependent constraint name.
After that disable child first and then parent constraint.

SELECT p.table_name "Parent Table", c.table_name "Child Table",
p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint"
FROM user_constraints p
JOIN user_constraints c ON(p.constraint_name=c.r_constraint_name)
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R' AND p.table_name = UPPER('&table_name')
/

The following query will generate a script to drop the child constraints

select 'alter table '||c.table_name||' disable constraint '||c.constraint_name||' ;'
FROM user_constraints p
JOIN user_constraints c ON(p.constraint_name=c.r_constraint_name)
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R' AND p.table_name = UPPER('&table_name')
/

2)Disable the constraint with cascade option.

SQL> alter table transaction disable constraint EMPLOYEE_PK cascade;

Track Progress of Database Restore

Find out the name of the restore point
1* select name,time from v$restore_point
SQL> /

NAME TIME
------------------------------ ----------------------------------------
JULY_01_2013 01-JUL-13 08.30.29.000000000 AM

SQL>

Startup the database in mount state
SQL> Shutdown immediate
SQL> startup mount

SQL> flashback database to restore point JULY_01_2013 ;


NOW, Track the Progress of the restore using

SQL> select sid,message from v$session_longops where sofar <> totalwork ;

SID
----------
MESSAGE
--------------------------------------------------------------------------------
1173
Flashback Database: Flashback Data Applied : 43160 out of 52292 Megabytes done


SQL>

Using QUERY with Data Pump Export - expdp

You can use QUERY within expdp to do a selective export of a table
Here's the exact way you need to format your query

QUERY=SCHEMA.table_name:single_quote double_quote QUERY_CLAUSE double_quote single_quote

Below EXAMPLE will export the entire schema SCOTT but from table EMP only rows having EMPNO >= 7900 would be exported.

mylx1 $ expdp scott/tiger directory=data_pump_dir dumpfile=abc.dmp QUERY=scott.emp:'"where EMPNO >= 7900"'

Export: Release 11.2.0.3.0 - Production on Wed Jun 5 08:39:56 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** directory=data_pump_dir dumpfile=abc.dmp query=scott.emp:"where empno>=7900"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . exported "SCOTT"."EMP"                               8.109 KB       3 rows
. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/usr/mydb1/expdp/abc.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 08:40:53

$

addnode gave PRCF-2023 : The following contents are not transferred as they are non-readable.

While adding a node to an existing four node cluster, got the following error.

Copying to remote nodes (Monday, May 20, 2013 8:37:18 AM EDT)
.........................................................................
WARNING:Error while copying directory /usr/local/opt/oracle/product/11.2.0.3 with exclude file list '/tmp/OraInstall2013-05-20_08-36-45AM/installExcludeFile.lst' 
to nodes 'myrac6d5'. [PRKC-PRCF-2015 : One or more commands were not executed successfully on one or more nodes : <null>]
----------------------------------------------------------------------------------
nmsis6d5:
PRCF-2023 : The following contents are not transferred as they are non-readable.
Directories:
-
Files:
1) /usr/local/opt/oracle/product/11.2.0.3/root.sh_11203

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

Refer to '/usr/local/opt/oracle/product/oraInventory/logs/addNodeActions2013-05-20_08-36-45AM.log' for details. 
You may fix the errors on the required remote nodes. Refer to the install guide for error recovery.

The issue in this case was the file permission on the file root.sh_11203

Solution
========
Fix the file permission so that its readable by the user owning the ORACLE_HOME and re-run the add_node command.

SQL Scripts to find TEMP tablespace usage

Here are various scripts which helps in determining who's using the TEMP tablespace.

Script 1

select a.inst_id,b.Total_MB, b.Total_MB - round(a.used_blocks*8/1024) Current_Free_MB,        
round(used_blocks*8/1024) Current_Used_MB, round(max_used_blocks*8/1024)             
Max_used_MB from gv$sort_segment a,  
(select round(sum(bytes)/1024/1024) Total_MB from dba_temp_files ) b; 

Script 2

SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM gv$session a, gv$tempseg_usage b, gv$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks

Script 3

select s.sid, s.osuser, s. process, s.sql_id, tmp.segtype, ((tmp.blocks*8)/1024)MB, tmp.tablespace
from gv$tempseg_usage tmp, gv$session s
where tmp.session_num=s.serial# and segtype in ('HASH','SORT')
order by blocks desc;

Script 4

select sql_id,sum(blocks) from gv$tempseg_usage group by sql_id order by 2 desc;

Useful Oracle Notes in reference to TEMP Tablespace

How Can Temporary Segment Usage Be Monitored Over Time? (Doc ID 364417.1)

TROUBLESHOOTING GUIDE (TSG) : ORA-1652: unable to extend temp segment [ID 1267351.1]

Generate AWR report for a particular SQL

Once you have identified that a particular SQL is causing issues, you can generate the AWR report for a particular SQL between two specific snap_id's for further analysis. SQL "awrsqrpt.sql" is located under $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 66397
Begin Snapshot Id specified: 66397

Enter value for end_snap: 66398
End   Snapshot Id specified: 66398

Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: 8p6wy9fr8m0sn
SQL ID specified:  8p6wy9fr8m0sn

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_2_66397_66398.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrsqlrpt_2_66397_66398.html

awrsqlrpt_2_66397_66398.html => This report gives lot of good information about that particular SQL including the Plan Statistics and EXECUTION PLAN

srvctl remove commands

SRVCTL REMOVE DATABASE - Removes a database configuration

Syntax and Options

srvctl remove database -d db_unique_name [-f]
-d db_unique_name
-f 
Force remove the database.

Example

srvctl remove database -d racdb

SRVCTL REMOVE INSTANCE - Removes a database instance configuration

Syntax and Options

srvctl remove instance -d db_unique_name -i inst_name [-f]
-d db_unique_name
-i inst_name
-f 
Force remove the database.

Example

srvctl remove instance -d racdb -i racdb01 -f

SRVCTL REMOVE NODEAPPS - Removes the node application configuration from the specified node. You must have
full administrative privileges to run this command. On Linux and UNIX systems, you must be logged in as root
and on Windows systems, you must be logged in as a user with Administrator privileges.

Syntax and Options

srvctl remove nodeapps -n node_name_list [-f]

Options Description 
-n node_name_list
 Node name or a comma-delimited list of node names.
-f
 Force remove.

Example

srvctl remove nodeapps -n "mynode1,mynode2,mynode3"

srvctl remove listener - Removes the listener from the specified node.

Syntax and Options

srvctl remove listener -n node_name [-l listener_name]

Options Description 
-n node_name
 Name of the node.
-l listener_name
 Name of the listener that you want to remove.

You will see something like below from $GRID_HOME/bin/crsstat output

HA Resource                                   Target     State
-----------                                   ------     -----
ora.LISTENER.lsnr                             ONLINE     ONLINE on myrac1d1
ora.LISTENER_SCAN1.lsnr                       ONLINE     ONLINE on myrac1d4
ora.myrac1d1.ASM1.asm                         ONLINE     ONLINE on myrac1d1
ora.myrac1d1.LISTENER_MYRAC1D1.lsnr           ONLINE     ONLINE on myrac1d1
ora.nmsis1d1.ons                              ONLINE     ONLINE on myrac1d1

Examples
The following command removes the listener LISTENER_MYRAC1D1 from the myrac1d1 node:

srvctl remove listener -n myrac1d1 -l LISTENER_MYRAC1D1

ORA-12012: error on auto execute of job : ORA-01878: specified field not found in datetime or interval

You may see the below errors in your alert log file after the daylight time changes if the job happens to run around
the time when the daylight time changes, which is 2 AM.

ORA-12012: error on auto execute of job 219820676
ORA-01878: specified field not found in datetime or interval

You can run the below queries to find out when the job was scheduled to run and who owns the job

select job ,to_char(LAST_DATE,'YYYYMMDD HH24:MI:SS'),to_char( NEXT_DATE ,'YYYYMMDD HH24:MI:SS') from dba_jobs where NEXT_DATE < sysdate;

select job, what, log_user, priv_user from dba_jobs where job=<job_number> ;

Connect to the database with the priv_user from the above query for that particular job and change the next_date manually by running the following

NOTE: If possible move the time of the job to away from 2 AM time, that is when the time change happens twice every year. I did moved the job to 04:00 AM so you are good for the future as well.

exec DBMS_JOB.NEXT_DATE(219820676,to_date('20130409 04:00:00','YYYYMMDD HH24:MI:SS')) ;

Create Restore Point and Recover the database using Restore Point

To create a flashback restore point, you must be using FRA and flashback must be turned on.
Check to see if flashback is turned on with the following:

Enabling Flashback Database

Step 1 . Set the parameters

ALTER SYSTEM SET  db_recovery_file_dest_size       = 750G   scope = spfile ;
ALTER SYSTEM SET  db_flashback_retention_target    = 240    scope = spfile ;

{ Default is 1440 minutes i.e. 24 hours. Start with 4 hour retention and this can be changed 
dynamically once we have the stats about how much flash back space we need per hour }

Step 2 . Shutdown the database

$ srvctl stop database –d myracdb

Step 3 . Startup mount the database (one node) and turn on Flash Back

SQL> STARTUP MOUNT;  --- > start only one instance manually
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;

Step 4 . Make Sure Flashback is Turned ON and shutdown the instance.

SQL> SELECT FLASHBACK_ON FROM v$database ;
SQL> SELECT * FROM v$bgprocess where name='RVWR';
SQL> SHUTDOWN IMMEDIATE ;

Step 5 . Start up RAC instances

$ srvctl start database –d myracdb

Recovery Writer process (RVWR)
The Recovery Writer process is responsible for writing to the flashback logs in the flash recovery area.

These logs are required to restore the database to a previous point in time by using the "flashback" option 
for Oracle databases (Oracle 10g and later).

Create Restore Point

SQL> CREATE RESTORE POINT before_migration GUARANTEE FLASHBACK DATABASE;

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION# DI,GUARANTEE_FLASHBACK_DATABASE,
     STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES' ;

Recover Dataabse with Restore Point

SQL> startup mount
SQL> flashback database to restore point before_migration;
SQL> SHUTDOWN IMMEDIATE

Set the Oracle environment back to the orignal version, run the following:

SQL> startup mount
SQL> alter database open resetlogs;
SQL> drop restore point before_migration;

PRVG-11050 : No matching interfaces "bond0" for subnet "90.xxx.127.0" on nodes "myrac1,myrac2,myrac3,myrac4"

I got the below errors while running the pre-checks before upgrading from 11gR1 (11.1.0.7) to 11gR2 (11.2.0.3)

PRVG-11050 : No matching interfaces "bond0" for subnet "90.xxx.127.0" on nodes "myrac1,myrac2,myrac3,myrac4"

Check: Node connectivity for interface "bond0"
Result: Node connectivity failed for interface "bond0"

Check the following on one of the nodes.

myrac1:/usr/local/opt/oracle/ $ oifcfg iflist -p -n
bond0 90.xxx.126.0 UNKNOWN 255.255.254.0
bond1 172.29.70.0 PRIVATE 255.255.255.0

myrac1:/usr/local/opt/oracle/ $ oifcfg getif
bond0 90.xxx.127.0 global public
bond1 172.29.70.0 global cluster_interconnect

You would notice that values for bond0 after running oifcfg getif
is different when you run oifcfg iflist -p -n

To solve the issue, work with your Network Admin to find out the real values for the bond0 and update.
In our case it should have been 90.xxx.126.0

Login as ROOT

# oifcfg delif -global bond0/90.xxx.127.0

# oifcfg setif -global bond0/90.xxx.126.0:public

Running the above should solve the issue.

RMAN - unregister database from recovery catalog

SQL> select db_key, db_name, reset_time, dbinc_status from RMANCAT.DBINC where db_name = 'MYRACDB' ;

DB_KEY DB_NAME RESET_TIME DBINC_ST
---------- -------- ----------- --------
91068668 MYRACDB 17-nov-2010 PARENT
91068668 MYRACDB 12-mar-2008 PARENT
91068668 MYRACDB 02-may-2011 CURRENT
91068668 MYRACDB 28-apr-2011 ORPHAN
91068668 MYRACDB 27-apr-2011 ORPHAN
91068668 MYRACDB 27-apr-2011 ORPHAN
91068668 MYRACDB 28-apr-2011 ORPHAN

7 rows selected.

SQL> select db_key, db_id from RMANCAT.DB where DB_KEY=91068668;

DB_KEY DB_ID
---------- ----------
91068668 232532794

Now Login as RMAN

${ORACLE_HOME}/bin/rman catalog rmancat/cat@rmandb.world.com

RMAN> set dbid=232532794;

RMAN> unregister database ;

database name is "MYRACDB" and DBID is 232532794

Do you really want to unregister the database (enter YES or NO)? yes
database unregistered from the recovery catalog

RMAN>

Database runInstaller "Nodes Selection" Window Does not Show RAC Nodes

Oracle Clusterware (CRS or GI) is up and running as confirmed by $CRS_HOME/bin/crsctl check crs on all nodes, and $CRS_HOME/bin/olsnodes -n show all the nodes, but database runInstaller does not show all cluster nodes.

There might be an issue with the Inventory for Clusterware home.

Look at inventory.xml file under the oraInventory/ContentsXML directory.
It should show CRS="true" against the correct CRS or GI home and only one entry should have CRS="true" even if there are multiple (older) CRS or GI homes listed.

Do not update the inventory.xml manually. Use the below commands to fix the issue.

$GRID_HOME/oui/bin/runInstaller -silent -ignoreSysPrereqs -updateNodeList ORACLE_HOME="/opt/app/oragrid/oracle/product/11.2.0.3" LOCAL_NODE="myracd1" CLUSTER_NODES="{myracd1,myracd2}" CRS=true

Change the LOCAL_NODE to point to the node from where you are running the command. This needs to be run from every node where you want the inventory.xml updated.

If another CRS_HOME also has CRS="true" as in example below.

then use the below command to set it to false.

$GRID_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME="/opt/app/t5cim1d/oracle/product/crs" CRS=false

Oracle Notes 1327486.1 and 1053393.1 has more details.

Test Box

Font 1
Font 2
Font 3
Font 4
Font 5

abc
abc
abc
abc

put
your
content
here

select job ,to_char(LAST_DATE,'YYYYMMDD HH24:MI:SS'),to_char( NEXT_DATE ,'YYYYMMDD HH24:MI:SS') from dba_jobs where NEXT_DATE < sysdate; select job, what, log_user, priv_user from dba_jobs where job= ;

select job ,to_char(LAST_DATE,'YYYYMMDD HH24:MI:SS'),to_char( NEXT_DATE ,'YYYYMMDD HH24:MI:SS') from dba_jobs where NEXT_DATE < sysdate; select job, what, log_user, priv_user from dba_jobs where job= ; select job ,to_char(LAST_DATE,'YYYYMMDD HH24:MI:SS'),to_char( NEXT_DATE ,'YYYYMMDD HH24:MI:SS') from dba_jobs where NEXT_DATE < sysdate; select job, what, log_user, priv_user from dba_jobs where job= ;

11gR1 to 11gR2 Upgrade: cluvfy tool found some mandatory patches are not installed

The cluvfy tool found some mandatory patches are not installed.
These patches need to be installed before the upgrade can proceed.
The pre-upgrade checks failed, aborting the upgrade

The above error is mis-leading sometimes. Look under the log file at
$GRID_HOME/cfgtoollogs/crsconfig and re-run the cluvfy commands listed there manually by removing the "-_patch_only"

/bin/su oragrid -c ' /opt/app/oragrid/oracle/product/11.2.0.3/bin/cluvfy stage -pre crsinst -n myrac1,myrac2 -upgrade -src_crshome /opt/app/oracle/product/crs -dest_crshome /opt/app/oragrid/oracle/product/11.2.0.3 -dest_version 11.2.0.3.0 '

if the above comes back without issues then the problem is the environment variables ORA_CRS_HOME. If that is set at the session from where you are running rootupgrade.sh, then you will see the above error.

unset ORA_CRS_HOME and re-run the rootupgrade.sh and it should finish without errors.

Oracle Note 1498538.1 has more details about it as well.

Oracle DB 11gR2 Global AWR Report Generation

Oracle DB 11gR2 AWR Global Report Generation

Before 11gR2, the awrrpt.sql under $ORACLE_HOME/rdbms/admin only generates awr report for local instance.
You have to collect awr report for each of RAC instances.

In 11gR2 there are two new scripts awrgrpt.sql AND awrgdrpt.sql for RAC

awrgrpt.sql -- AWR Global Report (RAC) (global report)
awrgdrpt.sql -- AWR Global Diff Report (RAC)

Some other important scripts under $ORACLE_HOME/rdbms/admin

spawrrac.sql -- Server Performance RAC report
awrsqrpt.sql -- Standard SQL statement Report
awrddrpt.sql -- Period diff on current instance
awrrpti.sql -- Workload Repository Report Instance (RAC)

PRCD-1231 : Failed to upgrade configuration of database and PRKC-1136

PROBLEM: After upgrading the database from 11.1.0.7 to 11.2.0.3 (using MANUAL Method), unable to update the CRS with new version of the database

rklx1:11gr2_upgrade/ $ srvctl upgrade database -d racdb -o /usr/local/opt/oracle/product/11.2.0.3
PRCD-1231 : Failed to upgrade configuration of database racdb to version 11.2.0.3.0 in new Oracle home /usr/local/opt/oracle/product/11.2.0.3
PRKC-1136 : Unable to find version for database with name racdb

rklx1:11gr2_upgrade/ $ srvctl remove database -d racdb
PRCD-1120 : The resource for database racdb could not be found.
PRCR-1001 : Resource ora.racdb.db does not exist

SOLUTION:

Login as ROOT to node 1

#$GRID_HOME/bin/./crs_unregister ora.racdb.racdbt4.inst
#$GRID_HOME/bin/./crs_unregister ora.racdb.racdbt3.inst
#$GRID_HOME/bin/./crs_unregister ora.racdb.racdbt2.inst
#$GRID_HOME/bin/./crs_unregister ora.racdb.racdbt1.inst
#$GRID_HOME/bin/./crs_unregister ora.racdb.db

Then Login as Oracle User and Add the database and the instance

srvctl add database -d racdb -o $ORACLE_HOME
srvctl add instance -d racdb -i racdbt1 -n rklx1
srvctl add instance -d racdb -i racdbt2 -n rklx2
srvctl add instance -d racdb -i racdbt3 -n rklx3
srvctl add instance -d racdb -i racdbt4 -n rklx4

and then start the database

srvctl start database -d racdb

PRVG-11055 : Interfaces configured with subnet number "90.xxx.xxx.0" have multiple subnets masks

Checking subnet mask consistency...
Subnet mask consistency check passed for subnet "172.29.70.0".
PRVG-11055 : Interfaces configured with subnet number "90.xxx.xxx.0" have multiple subnets masks
PRVG-11056 : subnet masks "255.255.254.0" are configured with subnet number "90.xxx.xxx.0" on nodes "rklx4,rklx3,rklx2,rklx1"
PRVG-11056 : subnet masks "255.255.255.0" are configured with subnet number "90.xxx.xxx.0" on nodes "rklx4,rklx3,rklx2,rklx1"
Subnet mask consistency check failed.

Result: Node connectivity check failed

SOLUTION
========

# $ORA_CRS_HOME/bin/oifcfg iflist -p -n

bond0 172.xx.xx.0 PRIVATE 255.255.255.0
bond1 90.xxx.xxx.0 UNKNOWN 255.255.254.0



# $ORA_CRS_HOME/bin/crs_stat -p ora.rklx1.vip =====> run this on all nodes of the cluster

You need to modify the subnet mask by running the following

srvctl modify nodeapps -n rklx1 -A 90.xxx.xxx.166/255.255.254.0/bond1

srvctl modify nodeapps -n rklx2 -A 90.xxx.xxx.61/255.255.254.0/bond1

srvctl modify nodeapps -n rklx3 -A 90.xxx.xxx.114/255.255.254.0/bond1

srvctl modify nodeapps -n rklx4 -A 90.xxx.xxx.133/255.255.254.0/bond1

How to remove Disks from Disk Group

=> Find out the group number and name :

SQL> select group_number, name from v$asm_diskgroup ;

GROUP_NUMBER NAME
------------ ------------------------------
1 DATA
2 RECOVERY
3 GRID

=> Find out the name of the disk belonging to GROUP_NUMBER=3 which is GRID Disk Group.

SQL> select DISK_NUMBER, name, failgroup, group_number from v$asm_disk where group_number=3 order by name ;

DISK_NUMBER NAME FAILGROUP GROUP_NUMBER
----------- -------------- ---------------------- ------------
0 ASM2_VMAX00639 ASM2_VMAX00639 3
1 ASM2_VMAX0063A ASM2_VMAX0063A 3

=> so from above, there are two disks belonging to GRID diskgroup, now we'll remove one of the disks from the diskgroup
=> Drop the Disk from diskgroup named GRID

SQL> alter DISKGROUP GRID drop disk ASM2_VMAX00639 ;


=>You can check the re-balance progress using below SQL

SQL> select * from v$asm_operation;

How to Display Directory Structure Linux/Unix

The below command displays the directory tree structure

ls -R | grep ":$" | sed -e 's/:$//' -e 's/[^-][^\/]*\//--/g' -e 's/^/ /' -e 's/-/|/'

More information at http://www.centerkey.com/tree/

Oracle RAC - OCR Backups

The backups are taken on the OCR Master node. The OCR Master can change over time. 1. The default OCR master is always the first node that's started in the cluster. 2. When OCR master (crsd.bin process) stops or restarts for whatever reason, the crsd.bin on surviving node with lowest node number will become new OCR master.

Object and Tablespace I/O

The V$SEGMENT_STATISTICS view can be used to gather the statistics you need on access patterns of database segments. To find the database segments that incur the most I/O, use a query similar to the following: select owner,object_name,tablespace_name,sum(value) as total_io_operations from v$segment_statistics where statistic_name in ('physical reads','physical reads direct', 'physical writes','physical writes direct') group by owner,object_name,tablespace_name order by total_io_operations;

SQL Query Optimizer

SQL Query Optimizer

Very interesting reading if you want to know how Oracle Processes the SQL statements and deliver the results back.

http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#i21299

SQL to find RMAN Backup Duration

select TO_CHAR(start_time,'yyyy-mm-dd hh24:mi:ss') Start_Time,
TO_CHAR(end_time,'yyyy-mm-dd hh24:mi:ss') End_Time , INPUT_TYPE, round(ELAPSED_SECONDS/60) MINUTES
from v$rman_backup_job_details order by Start_Time asc
/

START_TIME END_TIME INPUT_TYPE MINUTES
------------------------------ ------------------------------ ------------- ----------
2012-01-02 01:00:24 2012-01-02 01:43:34 DB INCR 43
2012-01-02 18:01:07 2012-01-02 19:10:20 ARCHIVELOG 69
2012-01-06 14:52:48 2012-01-06 20:11:54 DB FULL 319

ASM DG to Physical Disk Mapping

#!/bin/ksh
for i in `/etc/init.d/oracleasm listdisks`
do
v_asmdisk=`/etc/init.d/oracleasm querydisk -d $i | awk '{print $2}'`
v_minor=`/etc/init.d/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk '{print $1}'`
v_major=`/etc/init.d/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk '{print $2}'`
v_device=`ls -la /dev | grep $v_minor | grep $v_major | awk '{print $10}'`
echo "ASM disk $v_asmdisk based on /dev/$v_device [$v_minor $v_major]"
done

CRS Diagnostic Data Gathering

CRS Diagnostic Data Gathering

For 10gR2
=========
Ensure that the environment variable ORA_CRS_HOME is set to the CRS home
Ensure that the environment variable ORACLE_BASE is set
Ensure that the environment variable HOSTNAME is set to the name of the host.
$./diagcollection.pl -collect

For 11gR1
=========
Execute diagcollection.pl by passing the crs_home as the following
export ORA_CRS_HOME=/u01/crs
$ORA_CRS_HOME/bin/diagcollection.pl -crshome=$ORA_CRS_HOME --collect

For 11gR2
=========
Execute /bin/diagcollection.sh

NOTE: --nocore This option significantly reduces the size of the final file by excluding the core files

OS Watcher (OSW)
================
For platforms where Cluster Health Monitor is not available, OS Watcher can collect OS performance statistics.
The OS Watcher guide for Windows is found in Oracle Metalink Document 433472.1 - OS Watcher For Windows (OSWFW) User Guide. However, CHM for Windows is far superior to OS Watcher for Windows and should be used wherever possible.


For all other platforms, the OS Watcher user guide can be found in Document 301137.1

The OS Watcher output or the compressed output can be manually collected from the osw installation directories. Browsing the OSW output will show the server performance profile.
If OS Watcher is not running, then you can start the data collection manually from the osw installation directory:

nohup ./startOSW.sh &

OS Watcher should be in init.d to ensure that it starts automatically at server start.
The script tarupfiles.sh should be run regularly to compress the OS watcher data collection output. This should be configured in crontab.

Find out about dropped network packets

$ netstat -s
OR
$ ifconfig -a
the above gives information about "dropped network packets"

List files for a particular date

ls -latR | grep 'Jul 23'

Perl script to run any UNIX/LINUX command and email the output

The following script would run command "lsof -u oracle | wc -l" and then check for
the threshold value and if the threshold is exceeded, it will email the output.

#!/usr/bin/perl -w
use POSIX 'strftime';
my $date = strftime '%m-%d-%Y %H:%M:%S', localtime;
my $command = `/usr/sbin/lsof -u oracle | wc -l `;
my $host = `hostname`; chomp($host);
my $to = "abc\@yahoo.com";
my $title = "LSOF Threshold Exceeded" ;
my $from = "DBA\@yahoo.com";
my $subject = "Threshold lsof exceeded";
my $thresh = 10;

if( $command ge $thresh ) {
open(MAIL, "|/usr/sbin/sendmail -t ");

print MAIL "To: $to\n";
print MAIL "From: $from\n";
print MAIL "Subject: $title for host : $host\n";

print MAIL "$date\n HOSTNAME: $host\n LSOF Count: $command\n\n";
print MAIL "LSOF Count has Exceeded the threshold of $thresh";

close(MAIL);

}

Remove Job from another user : DBMS_IJOB.REMOVE

SQL> exec dbms_job.remove(40682);
BEGIN dbms_job.remove(40682); END;

*
ERROR at line 1:
ORA-23421: job number 40682 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 687
ORA-06512: at "SYS.DBMS_JOB", line 174
ORA-06512: at line 1


SQL> EXECUTE SYS.DBMS_IJOB.REMOVE (40682);

PL/SQL procedure successfully completed.

SQL>

Find session activity

select event,'/usr/ucb/ps -aux | grep'||spid,pga_used_mem,sid,a.serial#,b.inst_id,logon_time,a.username,module,last_call_et/60,subst
r(machine,1,20),process,sql_id
from gv$session a,gv$process b where addr=paddr
and status='ACTIVE'
and a.username is not null
and a.username = 'GCP_USER'
and a.inst_id=b.inst_id
and last_call_et/60 > 1
order by b.inst_id
/

Who's using the UNDO segments

SELECT TO_CHAR (s.SID) || ',' || TO_CHAR (s.serial#) sid_serial,
NVL (s.username, 'None') orauser, s.program, r.NAME undoseg,
t.used_ublk * TO_NUMBER (x.VALUE) / 1024 || 'K' "Undo"
FROM SYS.v_$rollname r,
SYS.v_$session s,
SYS.v_$transaction t,
SYS.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.NAME = 'db_block_size'
/

Find out who's locking the accounts

set lines 200
set pages 200

column USERNAME format a12
column OS_USERNAME format a12
column USERHOST format a25
column EXTENDED_TIMESTAMP format a40

SELECT USERNAME, OS_USERNAME, USERHOST, EXTENDED_TIMESTAMP
FROM SYS.DBA_AUDIT_SESSION WHERE returncode != 0 and username = '&Account_Locked'
and EXTENDED_TIMESTAMP > (systimestamp-1) order by 4 desc
/

Query to find HISTOGRAMS

select owner,table_name,histogram from DBA_TAB_COL_STATISTICS where
owner='SCOTT' and table_name='EMPLOYEE'

Default STATS Collection in 11g

- The GATHER_STATS_JOB Oracle’s default stats collection job does not exist in
11g (the name does not exist) as it was there in 10g. Instead it has been
included in Automatic Maintenance Tasks

- How to check, Oracle’s default stats collection job is enable or disabled


SQL> select CLIENT_NAME,status from DBA_AUTOTASK_CLIENT;

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

- How to disable if it is enabled (run below query to disable it). Below PL/SQL block has to be executed by SYS

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;

EXPDP - EXCLUDE Multiple TABLES and SCHEMAS

The below example gives syntax to EXCLUDE multiple tables and multiple schemas while doing a full database export using expdp

=== BEGIN expdp_exclude.par

DIRECTORY=DATA_PUMP_DIR
DUMPFILE=abc.dmp
LOGFILE=abc.log
FULL=Y
EXCLUDE=STATISTICS
EXCLUDE=TABLE:"IN ('NAME', 'ADDRESS' , 'EMPLOYEE' , 'DEPT')"
EXCLUDE=SCHEMA:"IN ('WMSYS', 'OUTLN')"

=== END expdp_exclude.par

In the above example parameter file; tables NAME and ADDRESS are owned by SCOTT and tables EMPLOYEE and DEPT are owned by HR
EXCLUDE=TABLE => You do not have to prefix the OWNER name, in fact, if you put the OWNER.TABLE_NAME, it would not work.
It will EXCLUDE all TABLES having the name mentioned in the list, even if more than one owner has the same object name.
For example: If ADDRESS table is owned by user SCOTT and user HR, that table will be EXCLUDED from both the users.

The above commands would work only via parameter file and would not work on the command line.


COMMAND LINE SYNTAX for EXPDP

expdp system/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=abc.dmp FULL=Y
EXCLUDE=TABLE:\"IN \(\'NAME\', \'ADDRESS\' , \'EMPLOYEE\' , \'DEPT\'\)\"
EXCLUDE=SCHEMA:\"IN \(\'WMSYS\', \'OUTLN\'\)\"

Find Current CPU or PSU Applied

mylx1:product/11.1.0/OPatch/ $ ./opatch lsinv -bugs_fixed | grep -i 'database psu'
8833297 9352179 Mon Sep 13 22:00:34 EDT 2010 DATABASE PSU 11.1.0.7.1 (INCLUDES CPUOCT2009)
9209238 9352179 Mon Sep 13 22:00:34 EDT 2010 DATABASE PSU 11.1.0.7.2 (INCLUDES CPUJAN2010)
9352179 9352179 Mon Sep 13 22:00:34 EDT 2010 DATABASE PSU 11.1.0.7.3 (INCLUDES CPUAPR2010)
mylx1:product/11.1.0/OPatch/ $

REM This script outputs the current CPU applied on the database.column action format a15
column action_time format a30
column comments format a35
column action format a20
set linesize 300

select comments,action_time,action
from
(select action,action_time,comments
from sys.registry$history
where action in ('CPU','APPLY')
order by action_time desc)
where comments <> 'view recompilation'
and rownum < 2
/
-- Output from above script --
COMMENTS ACTION_TIME ACTION
----------------------------------- ------------------------------ --------------------
PSU 11.1.0.7.3 09-AUG-10 09.14.20.562314 AM APPLY

Expdp Options

expdp system/******** schemas=SCOTT directory=SCOTT_DUMP dumpfile=scott.dmp logfile=scott.log EXCLUDE=TABLE:\"LIKE \'EMP%\'\", TABLE:\"LIKE \'%ABC%\'\"

if you just type EXCLUDE=TABLE:"LIKE 'EMP%'", TABLE:"LIKE '%ABC%'":

you will get the following error.

ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00911: invalid character

you need to include escape characters in the statement, e.g.:
EXCLUDE=TABLE:\"LIKE \'EMP%\'\", TABLE:\"LIKE \'%ABC%\'\" ,
this would exclude tables starting with EMP and any tables having ABC in their table name.

Using the NOT IN OPERATOR
EXCLUDE=TABLE:\"NOT IN \(\'ABC\',\'XYZ\'\)\"

Using the IN OPERATOR
EXCLUDE=TABLE:\"IN \(\'ABC\',\'XYZ\'\)\"

Find Unindexes FK Constraints

col table_name format a32
col columns format a40
set lines 140
set pages 200
select table_name, constraint_name,
cname1 || nvl2(cname2,','||cname2,null) ||
nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
columns
from ( select b.table_name,
b.constraint_name,
max(decode( position, 1, column_name, null )) cname1,
max(decode( position, 2, column_name, null )) cname2,
max(decode( position, 3, column_name, null )) cname3,
max(decode( position, 4, column_name, null )) cname4,
max(decode( position, 5, column_name, null )) cname5,
max(decode( position, 6, column_name, null )) cname6,
max(decode( position, 7, column_name, null )) cname7,
max(decode( position, 8, column_name, null )) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from user_cons_columns ) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name
) cons
where col_cnt > ALL
( select count(*)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4,
cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
group by i.index_name
)
order by table_name
/
(Credit goes to the original author, found it somewhere on internet)

FTS with Table Name

select distinct a.sql_id,b.object_name
--dbms_lob.substr(a.sql_text)
from dba_hist_sqltext a,
(select SQL_ID,object_name from dba_hist_sql_plan where object_owner='SCOTT'and OPERATION = 'TABLE ACCESS' and OPTIONS =
'FULL') b
where a.sql_id = b.sql_id
order by 1
/

Find SQLs doing Full Table Scans

select sql_id,sql_text from dba_hist_sqltext
where sql_id in (select distinct SQL_ID from dba_hist_sql_plan where object_owner='SCOTT'
and OPERATION = 'TABLE ACCESS' and OPTIONS = 'FULL')
/

crsctl.bin: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory

After upgrading the CRS to 11g (11.1.0.7) and at the time of running the root111.sh (at 11.1.0.7), got the below error

/usr/local/opt/oracrs/bin/crsctl.bin: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory

And found the workaround in the below note.

After Installing Patchset Crsctl Fails To Load Libclntsh.so [ID 333233.1]

Workaround was to manually change the permission of libclntsh.so.11.1 and After applying the workaround all services in the cluster were ONLINE.

Oracle RAC Commands

To shutdown RDBMS on all nodes run the following command:

$ORACLE_HOME/bin/srvctl stop database -d dbname

To shutdown RDBMS instance on the local node run the following command:

$ORACLE_HOME/bin/srvctl stop instance -d dbname -i instance_name

To shutdown ASM instances run the following command on each node:

$ORACLE_HOME/bin/srvctl stop asm -n ;

To shutdown listeners run the following command on each node:

$ORACLE_HOME/bin/srvctl stop listener -n ;

To shutdown nodeapps run the following comand on each node:

$ORA_CRS_HOME/bin/srvctl stop nodeapps -n ;

To shutdown CRS daemons on each node by running as root:

# crsctl stop crs

How to suppress Oracle Banner

Disabling "Banner" assumes significance in case of Oracle RAC Install. The result of not temporarily removing the banner is that the dba will see errors that say, "User equivalence failed for user oracle".

• Log in (or sudo to) user oracle
• cd ~/.ssh
• Modify (or create) a file named “config” in this directory, to add the following line (case-sensitive, left-justified):

LogLevel QUIET

• Save and close the file.
• Test to ensure that oracle can ssh to all other RAC nodes in the cluster, without being presented with a banner.


What is displayed as Banner is stored under /usr/localcw/opt/tcpwrapper/banners/

How to start Oracle runInstaller in TRACING mode

Launch the installer with tracing turned on

./runInstaller -J-DTRACING.ENABLED=true -J-DTRACING.LEVEL=2

How to Check OCR and Voting Disk

How to find out which raw devices are used for OCR and which ones are used for Voting Disk.

mylxd1->ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 487980
Used space (kbytes) : 3884
Available space (kbytes) : 484096
ID : 2006423852
Device/File Name : /dev/raw/raw1
Device/File integrity check succeeded
Device/File Name : /dev/raw/raw2
Device/File integrity check succeeded

Cluster registry integrity check succeeded

Logical corruption check succeeded

mylxd1->crsctl query css votedisk
0. 0 /dev/raw/raw3
1. 0 /dev/raw/raw4
2. 0 /dev/raw/raw5
Located 3 voting disk(s).

Database restart on HOST reboot

Create a script to stop/start the database

Execute these as ROOT.

cp {script to stop/start the database to} /etc/init.d/oracle
chmod 755 /etc/init.d/oracle
ln –s /etc/init.d/oracle /etc/rc0.d/K05oracle
ln –s /etc/init.d/oracle /etc/rc3.d/S90oracle

Delete archivelogs using RMAN until date

RMAN> run
{
DELETE archivelog until time "to_date('2010-08-23:10:00:00','YYYY-MM-DD:hh24:mi:ss')";
}

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

lxtestbox:/exp/expdp/ $ impdp system/password parfile=imp_from_test.par

Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 24 August, 2010 9:43:41

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/exp/expdp/test01.dmp" for read
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 3

Solution

Mount the file system with the following option

rw,noac,bg,intr,hard,timeo=600,wsize=32768,rsize=32768,nfsvers=3,tcp

RMAN Restore Point in Time Restore (PITR)

run {
allocate channel t1 type disk ;
allocate channel t2 type disk ;
allocate channel t3 type disk ;
allocate channel t4 type disk ;
set until time "to_date('2010-08-23 08:15:00','YYYY-MM-DD HH24:MI:SS')" ;
restore database ;
recover database ;
sql 'alter database open resetlogs' ;
release channel t1;
release channel t2;
release channel t3;
release channel t4;
}

Script to find foreign key constraints

Script to find foreign key constraints
select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
from all_constraints
where constraint_type='R'
and r_constraint_name in (select constraint_name from all_constraints
where constraint_type in ('P','U') and table_name='&TABLE_NAME')
/

Find Oracle Database Character Set

Character Sets
(Ordinary) character set
The (ordinary) character set for a database can be determined with:

SQL> select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

National character set
The national character set for a database can be determined with:

SQL> select value from nls_database_parameters
where parameter = 'NLS_NCHAR_CHARACTERSET';

How to find number of sessions per hour for EACH INSTANCE in a RAC

SELECT
to_char(TRUNC(s.begin_interval_time,'HH24'),'DD-MON-YYYY HH24:MI:SS') snap_begin,
r.instance_number instance,
r.current_utilization sessions
FROM
dba_hist_resource_limit r,
dba_hist_snapshot s
WHERE ( TRUNC(s.begin_interval_time,'HH24'),s.snap_id ) IN
(
--Select the Maximum of the Snapshot IDs within an hour if all of the snapshot IDs
--have the same number of sessions
SELECT TRUNC(sn.begin_interval_time,'HH24'),MAX(rl.snap_id)
FROM dba_hist_resource_limit rl,dba_hist_snapshot sn
WHERE TRUNC(sn.begin_interval_time) >= TRUNC(sysdate-1)
AND rl.snap_id = sn.snap_id
AND rl.resource_name = 'sessions'
AND rl.instance_number = sn.instance_number
AND ( TRUNC(sn.begin_interval_time,'HH24'),rl.CURRENT_UTILIZATION ) IN
(
--Select the Maximum no.of sessions for a given begin interval time
SELECT TRUNC(s.begin_interval_time,'HH24'),MAX(r.CURRENT_UTILIZATION) "no_of_sess"
FROM dba_hist_resource_limit r,dba_hist_snapshot s
WHERE r.snap_id = s.snap_id
AND TRUNC(s.begin_interval_time) >= TRUNC(sysdate-1)
AND r.instance_number=s.instance_number
AND r.resource_name = 'sessions'
GROUP BY TRUNC(s.begin_interval_time,'HH24')
)
GROUP BY TRUNC(sn.begin_interval_time,'HH24'),CURRENT_UTILIZATION
)
AND r.snap_id = s.snap_id
AND r.instance_number = s.instance_number
AND r.resource_name = 'sessions'
ORDER BY snap_begin,instance

How to find number of sessions per hour

SELECT
to_char(TRUNC(s.begin_interval_time,'HH24'),'DD-MON-YYYY HH24:MI:SS') snap_begin,
sum(r.current_utilization) sessions
FROM
dba_hist_resource_limit r,
dba_hist_snapshot s
WHERE ( TRUNC(s.begin_interval_time,'HH24'),s.snap_id ) IN
(
--Select the Maximum of the Snapshot IDs within an hour if more than one snapshot IDs
--have the same number of sessions within that hour , so then picking one of the snapIds
SELECT TRUNC(sn.begin_interval_time,'HH24'),MAX(rl.snap_id)
FROM dba_hist_resource_limit rl,dba_hist_snapshot sn
WHERE TRUNC(sn.begin_interval_time) >= TRUNC(sysdate-1)
AND rl.snap_id = sn.snap_id
AND rl.resource_name = 'sessions'
AND rl.instance_number = sn.instance_number
AND ( TRUNC(sn.begin_interval_time,'HH24'),rl.CURRENT_UTILIZATION ) IN
(
--Select the Maximum no.of sessions for a given begin interval time
-- All the snapshots within a given hour will have the same begin interval time when TRUNC is used
-- for HH24 and we are selecting the Maximum sessions for a given one hour
SELECT TRUNC(s.begin_interval_time,'HH24'),MAX(r.CURRENT_UTILIZATION) "no_of_sess"
FROM dba_hist_resource_limit r,dba_hist_snapshot s
WHERE r.snap_id = s.snap_id
AND TRUNC(s.begin_interval_time) >= TRUNC(sysdate-1)
AND r.instance_number=s.instance_number
AND r.resource_name = 'sessions'
GROUP BY TRUNC(s.begin_interval_time,'HH24')
)
GROUP BY TRUNC(sn.begin_interval_time,'HH24'),CURRENT_UTILIZATION
)
AND r.snap_id = s.snap_id
AND r.instance_number = s.instance_number
AND r.resource_name = 'sessions'
GROUP BY
to_char(TRUNC(s.begin_interval_time,'HH24'),'DD-MON-YYYY HH24:MI:SS')
ORDER BY snap_begin

Linux GUI

How to find system resource utilization in Linux

$ export DISPLAY=90.30.212.197:0.0
$ gnome-system-monitor

Kernel Parameters for RedHat Linux

$ ipcs -l

------ Shared Memory Limits --------
max number of segments = 4096 // SHMMNI
max seg size (kbytes) = 66046570 // SHMMAX
max total shared memory (kbytes) = 66046568 // SHMALL
min seg size (bytes) = 1

------ Semaphore Limits --------
max number of arrays = 128 // SEMMNI
max semaphores per array = 250 // SEMMSL
max semaphores system wide = 32000 // SEMMNS
max ops per semop call = 100 // SEMOPM
semaphore max value = 32767

------ Messages: Limits --------
max queues system wide = 16 // MSGMNI
max size of message (bytes) = 65536 // MSGMAX
default max size of queue (bytes) = 65536 // MSGMNB

>> Set shmmax to 0.5 * Total Memory (free -b)

>> SHMMAX is the maximum size of a shared memory segment on a Linux system
whereas SHMALL is the maximum allocation of shared memory pages on a system.

>> SHMALL is set to 8 GB by default (8388608 KB = 8 GB). If you have more physical memory than this,
and it is to be used for oracle database, then this parameter should be increased to approximately
80% of the physical memory. For instance, if you have a server with 16 GB of memory to be used primarily
for oracle, then 80% of 16 GB is 12.8 GB divided by 4 KB (the base page size). The ipcs output has converted
SHMALL into kilobytes. The kernel requires this value as a number of pages.

>> The next section "Semaphore Limits" covers the amount of semaphores available to the operating system.
The kernel parameter semaphore consists of 4 tokens, SEMMSL, SEMMNS, SEMOPM and SEMMNI.
SEMMNS is the result of SEMMSL multiplied by SEMMNI.
The database manager requires that the number of arrays (SEMMNI) be increased as necessary.
Typically, SEMMNI should be twice the maximum number of connections allowed (MAXAGENTS) multiplied by the
number of logical partitions on the database server plus the number of local application connections
on the database server.

>> Section "Messages: Limits" covers messages on the system.

MSGMNI affects the number of agents that can be started, MSGMAX affects the size of the message that can be
sent in a queue, and MSGMNB affects the size of the queue.

To modify these kernel parameters, we need to edit the /etc/sysctl.conf file.
for example:
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 67631687680
kernel.sem=250 32000 100 128
kernel.shmmni=4096
kernel.shmall=16511642

Name Description
------ --------------------------------------------------------
SHMMAX Maximum size of shared memory segment (bytes)
SHMMIN Minimum size of shared memory segment (bytes)
SHMALL Total amount of shared memory available (bytes or pages)
SHMSEG Maximum number of shared memory segments per process
SHMMNI Maximum number of shared memory segments system-wide
SEMMNI Maximum number of semaphore identifiers (that is, sets)
SEMMNS Maximum number of semaphores system-wide
SEMMSL Maximum number of semaphores per set
SEMMAP Number of entries in semaphore map
SEMVMX Maximum value of semaphore

(APEX) & the Embedded PL/SQL Gateway (EPG) in an 11G

After installing Oracle 11g, run the following to configure APEX
Run apxconf.sql from $ORACLE_HOME/apex
When prompted, enter the port for the Oracle XML DB HTTP server. The default port number is 8080.
Unlock the anonymous user
SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;

You should be able to log into apex as the admin user from a browser using -> http://machine.domain:port/apex

The machine is the DB host and the port is the one input during configure step.

If you get an error and can't log in, verify the EPG is up by running the following in your browser ->

http://machine.domain:port

If it's up, you should be prompted for a username and password for XDB.

If the EPG is not up, accomplish the following to start it:

1. Log in as SYS as SYSDBA
2. Run the following statement:
3. EXEC DBMS_XDB.SETHTTPPORT(port); ==>> Where port is the plsql gatway port.
4. COMMIT;

For example:
EXEC DBMS_XDB.SETHTTPPORT(8080);
COMMIT;

How to find size of LOB

Select b.table_name,b.Column_name,c.data_type,a.Segment_name,a."size"
from
(Select Segment_name , (bytes/(1024*1024*1024)) "size"
from User_Segments
where (bytes/(1024*1024*1024))>0.5 )a,
(Select Table_name,Column_name,Segment_name
from User_Lobs)b,
(Select table_name,Column_Name,Data_type from User_Tab_Columns
Where Data_Type in ('CLOB','BLOB','LONG','LONG RAW') ) c
Where a.segment_name=b.segment_name
and b.table_name=c.table_name
and b.column_name=c.column_name
Order by c.data_type
/

RMAN Backup on the Standby Database

Running RMAN Backup on the Standby Database

We can put the standby database in good use by running the RMAN backups there along with all the good reasons we have the standby database in place.

. If your Standby database is a Physical Standby database and you are taking backups ONLY on the physical standby database.

. The data file directories on the primary and standby database are identical.

. RMAN recovery catalog is required. Since the standby database has the same DBID as the primary database and is always from the same incarnation, the RMAN datafile backups are interchangeable.

. RMAN will connect to the standby database as target database. The backups taken can be used to restore the Primary Database.

. Primary database should not use Oracle Managed Files (OMF) for this to work. If we are using OMF then the file names of Primary and Standby could differ.

Configuration required on Primary and Standby Database.

. Configure Flash Recovery Area
. Use of SPFILE

Split the file in two

I have a file with 10 lines and want to split the file in two but with even rows in one file and odd rows in one file.

sed -n '2,${p;n;}' stat1.sql > even.sql
sed -n '1,${p;n;}' stat1.sql > odd.sql

Update table and commit every n rows

Declare

i integer;
x NUMBER ;
v_min NUMBER ;
v_max NUMBER ;

begin

select max(EMPID) into x from EMPLOYEE ;

v_min :=0 ;
v_max :=25000 ;

loop
update EMPLOYEE set CIO_NAME = 'JOHN' where EMPID >= v_min and EMPID < v_max ;
commit ;
v_min := v_min+25000 ;
v_max := v_min+25000 ;
if v_max > (x+30000) then
commit ;
dbms_output.put_line('All rows updated successfully ....') ;
exit ;
end if ;
end loop ;

Exception When others then
dbms_output.put_line('Error Occured ...') ;

end ;
/

Sequence cache misses were consuming significant database time

Many times looking at the AWR Report, you come across "Sequence cache misses were consuming significant database time" when there is a slow performance on inserts.

Try increasing the cache size of the Sequence and use noorder if you are running a RAC database. Increasing the cache size would help improve the performance of inserts.

More details to follow on this topic ......

Flashback Table to a time in the past

Scenario: Someone deleted some data accidently from a database accidently and now wants to get back the data erroneously deleted.

Solution: You can do flashback table to a particular point in time.
(Flashback Table uses undo segments to retrieve data, so all depends if the data is still there)

Login as schema owner and enable the row movement.

SQL> alter table EMPLOYEE enable row movement;

Get time stamp to which you want to go back and then

SQL> flashback table EMPLOYEE to timestamp to_timestamp('Jan 15 2009 10:00:00','Mon DD YYYY HH24:MI:SS');

Find all files having the string in Linux

To Find all files having the string "SPECIALMAIL"

find . -exec grep -i -l "SPECIALMAIL" {} \;

-i => Ignore Case

-l => List file names only

DataPump Command EXCLUDE/INCLUDE/REMAP_SCHEMA

Export the schema but leave two of the big tables out.

expdp scott/tiger DIRECTORY=DATA_PUMP dumpfile=scott%u.dmp filesize=5G JOB_NAME=SCOTT_J1 SCHEMAS=SCOTT EXCLUDE=TABLE:\"IN \(\'EMPLOYEE\', \'DEPT\'\)\"

You exported from SCOTT schema and now wanted to import some tables into a different schema (SMITH) and into different tablespaces

impdp SMITH/PASSWORD directory=data_pump dumpfile=scott%u.dmp REMAP_SCHEMA=SCOTT:SMITH REMAP_TABLESPACE=SCOTT_DATA:SMITH_DATA REMAP_TABLESPACE=SCOTT_IDX:SMITH_IDX TABLES=TABLE1, TABLE2, TABLE3

'gcs log flush sync' resolution

1)You fired an update statement on Instance-2.
2)However, the request for desired blocks was gone to Instance-1. So Instance-2 was waiting on 'gc cr request'.
3)Instance-1 had the requested blocks but before it ships the blocks to Instance-2, it need to flush the changes from current block to redo logs on disks. Until this is done Instance-2 waits on event - 'gcs log flush sync'.

The cause of this wait event 'gcs log flush sync' is mainly - Redo log IO performance.

To avoid this problem you need to =
1)Improve the Redo log I/o performance.
2) Set undersore parameter "_cr_server_log_flush" =false.

Performance - Isolating Waits in a RAC environment

Performance - Isolating Waits in a RAC environment.

Determine the snap IDs you are interested in

For example, to obtain a list of snap IDs from the previous day, execute the following SQL:

SQL> SELECT snap_id, begin_interval_time FROM dba_hist_snapshot WHERE TRUNC(begin_interval_time) = TRUNC(sysdate-1) ;

Step 1 :
--------
Identify the Wait Class

select wait_class_id, wait_class, count(*) cnt
from dba_hist_active_sess_history
where snap_id between &1 and &2
group by wait_class_id, wait_class
order by 3;
2723168908 Idle 1
3290255840 Configuration 9
3386400367 Commit 90
4108307767 System I/O 149
3875070507 Concurrency 182
1740759767 User I/O 184
1893977003 Other 244
4217450380 Application 365
2000153315 Network 475
[NULL] [NULL] 916
3871361733 Cluster 1844

Step 2
-------
Identify the event_id associated with above wait class ID
select event_id, event, count(*) cnt from dba_hist_active_sess_history
where snap_id between 18231 and 18232 and wait_class_id=3871361733
group by event_id, event
order by 3;
EVENT_ID EVENT COUNT(*)
1742950045 gc current retry 1
3897775868 gc current multi block request 1
512320954 gc cr request 4
661121159 gc cr multi block request 9
2685450749 gc current grant 2-way 11
3201690383 gc cr grant 2-way 18
1457266432 gc current split 27
3046984244 gc cr block 3-way 41
111015833 gc current block 2-way 62
3570184881 gc current block 3-way 62
737661873 gc cr block 2-way 67
2277737081 gc current grant busy 95
1520064534 gc cr block busy 235
2701629120 gc current block busy 396
1478861578 gc buffer busy 815

Step 3
-------
Identify the SQL_ID associated with the above event_id
select 'gc buffer busy' ,sql_id, count(*) cnt from dba_hist_active_sess_history
where snap_id between 18231 and 18232
and event_id in (1478861578)
group by sql_id having count(*) > 55
UNION
select 'gc current block busy',sql_id, count(*) cnt from dba_hist_active_sess_history
where snap_id between 18231 and 18232
and event_id in (2701629120)
group by sql_id having count(*) > 55
UNION
select 'gc cr block busy',sql_id, count(*) cnt from dba_hist_active_sess_history
where snap_id between 18231 and 18232
and event_id in (1520064534)
group by sql_id having count(*) > 55
order by 2 ;

Wait Event SQL ID waits
--------------------------------------------------------------------------------------
gc buffer busy 5qwhj3nru2jtq 765
gc current block busy 5qwhj3nru2jtq 332

Step 4 :
--------
Identify the SQL statement associated with the above SQL ID
select sql_id,sql_text from dba_hist_sqltext where sql_id in ('5qwhj3nru2jtq')
Output:
INSERT INTO Component_attrMap (Component_id, key, value) VALUES (:1, :2, :3)
Step 5 :
--------
Identify the object associated with the above statement
select current_obj#, count(*) cnt from dba_hist_active_sess_history
where snap_id between 18231 and 18232
and event_id in (1478861578,2701629120)and sql_id='5qwhj3nru2jtq'
group by current_obj#
order by 2;

Obj # Count(*)
67818 1
67988 1096

Step 6 :
-------
Identify the Object associated with the above Object ID
select object_id, owner, object_name, subobject_name, object_type from dba_objects
where object_id in (67988);
OBJECT_ID OWNER OBJECT_NAME SUBOBJECT_NAME
--------- ----- ------------- --------------
67988 SCOTT COMP_ID_INDX1 INDEX

In this case creating a REVERSE KEY index provided the required solution.