Wednesday, September 30, 2015
Monday, September 28, 2015
Schema Size
select
sum(bytes)/1024/1024/1024 as size_in_gig,
segment_type
from
dba_segments
where
owner='SCOTT'
group by
segment_type;
sum(bytes)/1024/1024/1024 as size_in_gig,
segment_type
from
dba_segments
where
owner='SCOTT'
group by
segment_type;
Scan listener down even if it is up
Some times oem shows scan listener status down even if it is up.
Problem is with configuration.
While creating a listener_scan target in oem it stores internally on which host the listener is active.
When a scan listener is failed over to another node and still up on oem it shows down as in oem config it is checking on a different node.
If that is the case
do the following
In my case listener_scan3 is showing down
bash-3.2$ ./srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node hmaistmsdb2
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node hmaistmsdb1
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node hmaistmsdb1
scan3 is running on node1 but in oem its pointing towards node 2
So we need to relocate the scan listener to node 2.
bash-3.2$ ./srvctl relocate scan_listener -i 3 -n hmaistmsdb2
3 refers to listener_scan3 in above
Problem is with configuration.
While creating a listener_scan target in oem it stores internally on which host the listener is active.
When a scan listener is failed over to another node and still up on oem it shows down as in oem config it is checking on a different node.
If that is the case
do the following
In my case listener_scan3 is showing down
bash-3.2$ ./srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node hmaistmsdb2
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node hmaistmsdb1
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node hmaistmsdb1
scan3 is running on node1 but in oem its pointing towards node 2
So we need to relocate the scan listener to node 2.
bash-3.2$ ./srvctl relocate scan_listener -i 3 -n hmaistmsdb2
3 refers to listener_scan3 in above
Monday, September 21, 2015
Wednesday, September 2, 2015
Encrypted tablespace creation and moving tables to encrypted TBS
CREATE TABLESPACE HMA_ENCRYPT_DATA DATAFILE
'+DATA_DG' SIZE 10G AUTOEXTEND OFF
LOGGING
ENCRYPTION USING 'AES256'
DEFAULT STORAGE (ENCRYPT)
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
'+DATA_DG' SIZE 10G AUTOEXTEND OFF
LOGGING
ENCRYPTION USING 'AES256'
DEFAULT STORAGE (ENCRYPT)
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
After that create table using following
create table test(id number) tablespace HMA_ENCRYPT_DATA;
or
We can move existing table using
alter table STG_VEHICLE_STATUS_HMMA_UPD move tablespace hma_encrypt_data;
After moving tables indexes become unsuable. So rebuild index as follows
alter index cool rebuild tablespace hma_encrypt_index;
Tuesday, September 1, 2015
kill oracle sql session at os level
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND' and SID=1444;
replace sid with your sid.
then login to server
kill -9 spid
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND' and SID=1444;
replace sid with your sid.
then login to server
kill -9 spid
Subscribe to:
Posts (Atom)