SELECT concat('+'||gname, sys_connect_by_path(aname, '/')) full_path,
gnumber group_number, gname group_name, file_number,
system_created, nvl(file_type, 'Directory') file_type,
decode(file_type, null, 'N', decode (system_created, 'N', 'Y', 'N')) file_alias,
block_size, blocks, bytes, space, redundancy, striped, creation_date,
modification_date
, redundancy_lowered
FROM (SELECT g.name gname, g.group_number gnumber, a.parent_index pindex, a.name aname,
a.reference_index rindex , a.system_created, a.alias_directory,
f.file_number,
f.type file_type, f.block_size, f.blocks, f.bytes, f.space, f.redundancy, f.striped, f.creation_date, f.modification_date
,f.redundancy_lowered
FROM v$asm_alias a, v$asm_diskgroup g, v$asm_file f
WHERE a.group_number = g.group_number
and a.group_number = 20
and a.group_number = f.group_number (+)
and a.file_number = f.file_number (+))
START WITH (mod(pindex, power(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex
Oracle
Oracle – FRA – display usage of flash recovery area
select round(space_used/(1024*1024*1024),2) GBUsed,space_limit/(1024*1024*1024) MaxGB from v$recovery_file_dest -- init parameter log_archive_dest_1 must be equal to LOCATION=USE_DB_RECOVERY_FILE_DEST -- and db_recovery_file_dest must be set select * from v$flash_recovery_area_usage
Oracle RAC – Display differences between instance initialization parameter values
SELECT p1.name, p1.value val_inst1, p2.value val_inst2
FROM gv$parameter p1
JOIN gv$parameter p2 ON p1.name = p2.name
WHERE p1.inst_id = 1
AND p2.inst_id = 2
AND p1.value != p2.value
AND p1.name NOT IN ('instance_number', 'instance_name',
'local_listener','thread','undo_tablespace','core_dump_dest')
Oracle – RMAN DROP DATABASE
-- set ORACLE env first
export ORACLE_SID=ORCL12
export ORACLE_HOME=/oracle/.....
oracle@oravm:~/ [ORCL12] rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Dec 27 09:45:33 2017
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup force mount;
Oracle instance started
database mounted
Total System Global Area 4275781632 bytes
Fixed Size 5218048 bytes
Variable Size 2415919360 bytes
Database Buffers 1845493760 bytes
Redo Buffers 9150464 bytes
RMAN> show db_unique_name;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL12 are:
RMAN configuration has no stored or default parameters
RMAN> SQL 'ALTER SYSTEM ENABLE RESTRICTED SESSION';
sql statement: ALTER SYSTEM ENABLE RESTRICTED SESSION
RMAN> DROP DATABASE INCLUDING BACKUPS NOPROMPT;
database name is "ORCL12" and DBID is 2242343723
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
specification does not match any backup in the repository
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any control file copy in the repository
List of Archived Log Copies for database with db_unique_name ORCL12
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - --------
1 1 1 A 30.11.17
Name: +ORA_DATA/ORCL12/ARCHIVELOG/2017_11_30/thread_1_seq_1.407.961431639
2 1 2 A 30.11.17
Name: +ORA_DATA/ORCL12/ARCHIVELOG/2017_11_30/thread_1_seq_2.414.961431685
3 1 3 A 30.11.17
Name: +ORA_DATA/ORCL12/ARCHIVELOG/2017_12_01/thread_1_seq_3.316.961561207
deleted archived log
archived log file name=+ORA_DATA/ORCL12/ARCHIVELOG/2017_11_30/thread_1_seq_1.407.961431639 RECID=1 STAMP=961431639
deleted archived log
archived log file name=+ORA_DATA/ORCL12/ARCHIVELOG/2017_11_30/thread_1_seq_2.414.961431685 RECID=2 STAMP=961431684
deleted archived log
archived log file name=+ORA_DATA/ORCL12/ARCHIVELOG/2017_12_01/thread_1_seq_3.316.961561207 RECID=3 STAMP=961561206
Deleted 3 objects
database name is "ORCL12" and DBID is 2242343723
database dropped
RMAN>
Linux – close file descriptors without killing process
-- get list of file descriptors # lsof /dev/mapper/XYZ_* -- example output oracle 31814 oracle 256u BLK 253,7 0t0 20968 /dev/mapper/../dm-7 -- above, fd 256 is open for update (could also be w (write)) -- attach to process with gdb debugger # gdb -p 31814 -- close file descriptor(s) gdb> p close(256)
Oracle – find sessions using database links
Run on both databases: select /*+ ORDERED */ substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10) "ORIGIN", substr(g.K2GTITID_ORA,1,35) "GTXID", substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" , s2.username, substr( decode(bitand(ksuseidl,11), 1,'ACTIVE', 0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'), 2,'SNIPED', 3,'SNIPED', 'KILLED' ),1,1 ) "S", substr(s2.event,1,10) "WAITING" from x$k2gte g, x$ktcxb t, x$ksuse s, v$session s2 where g.K2GTDXCB =t.ktcxbxba and g.K2GTDSES=t.ktcxbses and s.addr=g.K2GTDSES and s2.sid=s.indx;
Oracle ASM – Commands
SQL
select * from v$asm_diskgroup order by free_mb
asmcmd
asmcmd lsdg|awk '{sub(/\//,"",$14);print}'|tr -dc '[:print:]\n'|awk -v date="$(date +"%Y-%m-%d %r")" '{print $14 "\t\t" $9"\t\t"$8" : " date}'|sort -k 2 -n
Oracle Virtual Machine Manager
Also see: Troubleshoot high cpu usage – Java Thread
OVM Manager consists of a MySQL database and a Weblogic Server.
I have found that the cpu usage is at nearly 100% on the OVMM Linux server.
After some investigation I see that the java thread that causes most of the cpu usage belongs to CLIMain
and the thread is “EchoShell”
It is the OVM CLI – when I stop the service (/sbin/service ovmcli stop), the high cpu usage disappears.
The Command Line Interface (CLI) is used to enter commands to manage the VMs, repositories, NICs, etc.
The high cpu usage occurs when the CLI times out after 45 minutes – will test it and then use the “-o ServerAliveInterval=40” option for ssh connection in an attempt to avoid the switch to high cpu usage
High cpu usage definitely starts when timeout occurs –
the “-o ServerAliveInterval=40” option for ssh connection keeps the session alive,
no timeout, and no high cpu.
Oracle – RAC cluvfy
cluvfy stage -post crsinst -n nserver-rac1,server-rac2
Oracle – Display Contextual Information in sqlplus session (using sys_context)
Find username, db_name etc. from sqlplus session
select sys_context ('USERENV', 'SESSION_USER') from dual;
select sys_context ('USERENV', 'DB_NAME') from dual;