SELECT s.sid, s.serial#, s.username, s.osuser, p.spid "Linux process id", s.machine, p.terminal, s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr;
-- Get Linux processes that have no related session(s)
select inst_id, spid, program from gv$process where addr not in ( select paddr from gv$session ) and spid is not null
-- Generate Linux kill commands for processes that have no sessions (sessions that may have been killed at Oracle level
select p.inst_id,s.sid, s.serial#, s.program, s.username, p.spid, p.username,
'kill -9 '||p.spid
from
gv$session s
RIGHT OUTER JOIN
gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
where p.spid in(
SELECT spid
FROM gv$process
WHERE NOT EXISTS (SELECT 1
FROM gv$session
WHERE paddr = addr));
Oracle Technical
Posts about Oracle databases
Oracle – Find Oracle Inventory
Solaris: /var/opt/oracle/oraInst.loc Linux: /etc/oraInst.loc Windows: registry key: \\HKEY_LOCAL_MACHINE\\Software\Oracle\inst_loc
Oracle – Quick Check using Scripts
Not exhaustive
select * from v$sga_target_advice
select * from v$pga_target_advice
select * from v$shared_pool_advice
-- Dictionary Cache Hit Ratio
SELECT (1 - (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100
FROM v$rowcache;
-- -----------------------
-- Library Cache Hit Ratio
-- -----------------------
SELECT (1 -(Sum(reloads)/(Sum(pins) + Sum(reloads)))) * 100
FROM v$librarycache;
-- -------------------------------
-- DB Block Buffer Cache Hit Ratio
-- -------------------------------
SELECT (1 - (phys.value / (db.value + cons.value))) * 100
FROM v$sysstat phys,
v$sysstat db,
v$sysstat cons
WHERE phys.name = 'physical reads'
AND db.name = 'db block gets'
AND cons.name = 'consistent gets';
-- ---------------
-- Latch Hit Ratio
-- ---------------
SELECT (1 - (Sum(misses) / Sum(gets))) * 100
FROM v$latch;
-- -----------------------
-- Disk Sort Ratio
-- -----------------------
SELECT (disk.value/mem.value) * 100
FROM v$sysstat disk,
v$sysstat mem
WHERE disk.name = 'sorts (disk)'
AND mem.name = 'sorts (memory)';
-- Locks
SELECT a.serial# as serial,a.sid,a.username,b.type,b.ctime,lmode,a.osuser,c.sql_text
FROM v$session a,v$lock b, v$sqlarea c WHERE b.type in ('TM','TX','UL') and a.sid=b.sid and
lmode > 0 and ((a.PREV_HASH_VALUE = c.hash_value and a.prev_sql_addr = c.address and a.sql_hash_value = 0) or
(c.hash_value=a.sql_hash_value and c.address = a.sql_address))
Oracle – Dataguard, set flashback ON|OFF
It may be required to set flashback off temporarily - To remove flashback log files to gain space in the flash recovery area - To import schemas without generating flashback log files Flashback cannot be disabled on a standby database that is in the state 'APPLY-ON' Steps to set flashback off Primary 1. dgmgrl 2. connect as sys 3. edit database sby set state='APPLY-OFF'; 4. sqlplus / as sysdba 5. alter database set flashback off; 6. select flashback_on from v$database; --check status Standby 1. sqlplus / as sysdba 2. alter database set flashback off; 3. select flashback_on from v$database; --check status Primary and Standby (if flashback required) 1. alter database set flashback on; 2. select flashback_on from v$database; --check status Primary1. dgmgrl 2. connect as sys 3. edit database sby set state='APPLY-ON'; 4. show configuration;
Oracle – Tuning and Diagnostics Pack
Oracle – Licensing
Oracle Licensing
Oracle licensing is a relatively complex topic. The following links point to articles and documents that I have come across in my attempt to understand it. There are many potential scenarios, particularly with backup and recovery, disaster recovery and failover, and virtualization and clustering. Even after reading the articles and documents, and the Oracle documentation, it is probably necessary to have discussions with Oracle representatives to determine whether a particular scenario or architecture is correctly licensed. As well as database (engine/software) licenses, attention must be paid to database options and tuning packs.
Oracle Database Licensing
Oracle’s Licensing Rules – 5 Common pitfalls (techrepublic)
Oracle Licensing Information (pdf)
Oracle Software Investment Guide (pdf)
VMWare and Oracle Licensing
Oracle Database Licensing – raises questions
Oracle – test a database link of a user as sys user
1. A database link exists in the current database owned by CURRENTSCHEMA user (you don't know the password of the CURRENTSCHEMA user)
The database link was created by the application owner as follows:
CREATE DATABASE LINK "'DBLINKNAME.DOMAIN.COM"
CONNECT TO "TARGETSCHEMA" IDENTIFIED by password
USING 'TNSNAMES_ALIAS';
2. Create a table in the target database as a snapshot of the current v$session dynamic view
create table x as select * from v$session;
3. grant select on the table to TARGETSCHEMA
grant select on x to targetschema;
4. Use anonymous pl/sql block below to execute a command on the new table to prove that the database link
exists, works, and is pointing at the right target database.
set serverout on size unlimited (if using sqlplus)
declare
strresult varchar2(1024);
stmt_cursor NUMBER;
rc NUMBER;
found NUMBER := 0;
p_schema VARCHAR2(30) := 'CURRENTSCHEMA';
p_obj VARCHAR2(30) := 'DBLINKNAME.DOMAIN.COM';
uid dba_users.user_id%TYPE;
BEGIN
select user_id into uid from dba_users where username=p_schema;
stmt_cursor := dbms_sql.open_cursor;
sys.dbms_sys_sql.parse_as_user(stmt_cursor,
'BEGIN SELECT COUNT(*) INTO :found
FROM user_db_links
WHERE db_link = :p_obj;
EXCEPTION WHEN OTHERS THEN :found := 0;
END;', dbms_sql.NATIVE,
uid);
--dbms_sql.bind_variable(stmt_cursor, ':p_schema', p_schema);
dbms_sql.bind_variable(stmt_cursor, ':p_obj', p_obj);
dbms_sql.bind_variable(stmt_cursor, ':found', found);
rc := dbms_sql.execute(stmt_cursor);
dbms_sql.variable_value(stmt_cursor, ':found', found);
dbms_sql.close_cursor(stmt_cursor);
dbms_output.put_line('FOUND: '||found);
IF found = 1 THEN
stmt_cursor := dbms_sql.open_cursor;
sys.dbms_sys_sql.parse_as_user(stmt_cursor, 'select machine from sys.x@'||p_obj, dbms_sql.NATIVE,uid);
sys.dbms_sys_sql.define_column(stmt_cursor,1,strresult,1000);
rc := dbms_sys_sql.execute(stmt_cursor);
dbms_output.put_line('rc is: '||to_char(rc));
loop
if ( dbms_sys_sql.fetch_rows(stmt_cursor) > 0 )
then
dbms_sys_sql.column_value(stmt_cursor, 1,strresult );
dbms_output.put_line('table_name is: '||strresult);
else
exit;
end if;
end loop;
dbms_sql.close_cursor(stmt_cursor);
DBMS_OUTPUT.PUT_LINE(stmt_cursor);
END IF;
END;
/
Oracle – Flashback – set off and on to purge standby database flashback log files
1. Stop redo to standby 2. Set flashback off on standby 3. Set flashback on on standby 4. Start redo apply to standby On standby idle>select * from v$flash_recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID ----------------------- ------------------ ------------------------- --------------- ---------- CONTROL FILE 0 0 0 0 REDO LOG 0 0 0 0 ARCHIVED LOG .16 0 4 0 BACKUP PIECE .01 0 1 0 IMAGE COPY 0 0 0 0 FLASHBACK LOG 46.95 0 64 0 FOREIGN ARCHIVED LOG 0 0 0 0 AUXILIARY DATAFILE COPY 0 0 0 0 On Primary: Use dgmgrl: DGMGRL> connect sys@ORCL Password: Connected as SYSDBA. DGMGRL> edit database orclstby set state='apply-off'; Succeeded. On standby: idle>ALTER DATABASE FLASHBACK OFF; Database altered. idle>alter database flashback on; Database altered. idle>select * from v$flash_recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID ----------------------- ------------------ ------------------------- --------------- ---------- CONTROL FILE 0 0 0 0 REDO LOG 0 0 0 0 ARCHIVED LOG .22 0 7 0 BACKUP PIECE .01 0 1 0 IMAGE COPY 0 0 0 0 FLASHBACK LOG 1.5 0 2 0 FOREIGN ARCHIVED LOG 0 0 0 0 AUXILIARY DATAFILE COPY 0 0 0 0 On Primary: DGMGRL> edit database orclstby set state='apply-on'; Succeeded.
Oracle – Unified Auditing Trail – AUDSYS
The AUDIT_TRAIL needs to be purged. Error may occur if USE_LAST_ARCH_TIMESTAMP => TRUE is used: ERROR at line 1: ORA-08180: no snapshot found based on specified time ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 4425 ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 825 ORA-06512: at line 2 (Haven't investigated further) - maybe archive first is required or the archive timestamp has to be set. Purge example: SYS: ORCL> select segment_name||' '||bytes/1024/1024 from dba_segments where owner = 'AUDSYS'; SEGMENT_NAME||''||BYTES/1024/1024 -------------------------------------------------------------------------------- SYS_LOB0000076986C00014$$ 62.125 SYS_IL0000076986C00014$$ .0625 CLI_SCN$a48dc988$1$1 2 CLI_TIME$a48dc988$1$1 2 CLI_LOB$a48dc988$1$1 2 CLI_SWP$a48dc988$1$1 88 6 rows selected. Elapsed: 00:00:00.09 SYS: ORCL> BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, USE_LAST_ARCH_TIMESTAMP => FALSE); END; / PL/SQL procedure successfully completed. Elapsed: 00:00:04.55 SYS: LASD1> select segment_name||' '||bytes/1024/1024 from dba_segments where owner = 'AUDSYS'; SEGMENT_NAME||''||BYTES/1024/1024 -------------------------------------------------------------------------------- CLI_SWP$a48dc988$1$1 .0625 CLI_LOB$a48dc988$1$1 .0625 CLI_TIME$a48dc988$1$1 .0625 CLI_SCN$a48dc988$1$1 .0625 SYS_IL0000076986C00014$$ .0625 SYS_LOB0000076986C00014$$ .125 6 rows selected. Elapsed: 00:00:00.10
Oracle – Database Character Set
Important initialization parameter to be determined before database creation. select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET' SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;