impdb error

col owner_name for a30; col job_name for a30; col operation for a30; col state for a20; col job_mode for a20; SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%' ORDER BY 1,2; SELECT o.status, o.object_id, o.object_type, o.owner||'.'||object_name "OWNER.OBJECT" FROM dba_objects o, dba_datapump_jobs j WHERE o.owner=j.owner_name AND o.object_name=j.job_name AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;

MySQL memory change

SHOW ENGINE INNODB STATUS; BUFFER POOL AND MEMORY ---------------------- Total memory allocated 135987200; in additional pool allocated 0 Dictionary memory allocated 1124711 Buffer pool size 8191 Free buffers 3303 Database pages 4833 Old database pages 1804 After changes vi /etc/mysql/my.cnf # Added innodb_buffer_pool_size = 512M /etc/init.d/mysql stop /etc/init.d/mysql start BUFFER POOL AND MEMORY ---------------------- Total memory allocated 543948800; in additional pool allocated 0 Dictionary memory allocated 890055 Buffer pool size 32767 Free buffers 29924 Database pages 2840 Old database pages 1068

RMAN session / rman longops

RMAN backup status, RMAN jobs, rman back list, Is rman running, rman session, rman process -- With time (RAC version) col "Sid Serial#" for a15 col "Logon time" for a20 col spid for a6 select b.sid||','||b.serial# as "Sid Serial#", a.inst_id, b.status, a.spid, to_char(b.logon_time, 'dd Mon yyyy, hh24:mi') as "Logon time", b.client_info from gv$process a, gv$session b where a.addr=b.paddr and a.inst_id = b.inst_id and lower(client_info) like '%rman%'; -- Basic and (Non-RAC) select b.sid, b.serial#, a.spid, b.client_info from v$process a, v$session b where a.addr=b.paddr and client_info like '%rman%';

Is database standby

Quick check to see if the instance is standby or primary? SQL> select database_role from v$database; DATABASE_ROLE ---------------- PHYSICAL STANDBY SQL> select controlfile_type from V$database; CONTROL ------- STANDBY Useful sql select to_char(max(next_time),'dd Mon yyyy hh24:mi:ss') from v$archived_log where applied = 'YES'; TO_CHAR(MAX(NEXT_TIME),'DDMON ----------------------------- 05 Mar 2019 07:24:02 alter session set nls_date_format='dd Mon yyyy hh24:mi:ss'; select * from ( SELECT FIRST_TIME, NEXT_TIME, SEQUENCE# FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# desc) where rownum < 6 order by SEQUENCE#; FIRST_TIME NEXT_TIME SEQUENCE# ----------------------------- ----------------------------- ---------- 05 Feb 2019 06:39:02 05 Feb 2019 06:54:02 213003 05 Feb 2019 06:54:02 05 Feb 2019 07:09:02 213004 05 Feb 2019 07:09:02 05 Feb 2019 07:24:02 213005 05 Feb 2019 07:24:02 05 Feb 2019 07:39:02 213006 05 Feb 2019 07:39:02 05 Feb 2019 07:54:02 213007

Kill session

-- Current session alter system kill session '213,98643'; alter system kill session '777,56244,@1'; -- Kill session from another instance alter system kill session '66,64486,@2'; alter system kill session '66,64486' IMMEDIATE;

Schema Last analysed | Stale Stats

-- Summary of stale select stale_stats, count(*) from user_tab_statistics group by stale_stats; STA COUNT(*) --- ---------- 414 NO 2582 YES 199 select TABLE_NAME, STALE_STATS from user_tab_statistics where stale_stats = 'YES';

SQL for RAC db

RAC sql RAC Instances, DB instances, instance status, List of all instances, with startup time, up till, host load. col host_name for a16 select i.instance_number, i.host_name, i.instance_name, to_char(startup_time, 'DD Mon YYYY HH24:Mi') startup_time, floor(sysdate - startup_time) uptill, i.status, round(o.value, 3) value from gv$instance i, gv$osstat o where i.instance_number = o.inst_id and o.stat_name = 'LOAD' order by instance_number; -- e.g. INSTANCE_NUMBER HOST_NAME INSTANCE_NAME STARTUP_TIME UPTILL STATUS VALUE --------------- ---------------- ---------------- -------------------------- ---------- ------------ ---------- 1 proddbx00101 dex011 12 Feb 2018 02:52 247 OPEN 15.979 2 proddbx00102 dex012 12 Feb 2018 05:25 247 OPEN 18 3 proddbx00103 dex013 12 Feb 2018 07:23 247 OPEN 21.59 4 proddbx00104 dex014 12 Feb 2018 09:37 247 OPEN 19.079

Current Transaction for RAC set line 250 col "username_sid_inst" for a35 select s.username||'('||s.sid||', '||s.inst_id||')' username_sid_inst,s.sid,t.start_time, t.used_ublk as "Block Count", round(t.used_ublk*8192/1024/1024,2) Size_MB from gv$transaction t,gv$session s where s.saddr=t.ses_Addr and t.inst_id=s.inst_id order by start_time;

Session Wait for RAC set line 250 col username for a24 col event for a45 col 'FileId# Obj#' for a16 select s.inst_id, s.username, s.row_wait_file# ||', '|| s.ROW_WAIT_OBJ# as "FileId# Obj#", s.sql_hash_value, sw.event, sw.wait_time, sw.seconds_in_wait, sw.state from gv$session s, gv$session_wait sw where s.sid = sw.sid and s.inst_id = sw.inst_id and sw.wait_time != 0 order by s.inst_id, s.username;

Sessions connected for RAC -- Group by User (Summary) select s.username, count(*) from gv$session s group by s.username order by s.username; -- Group by Instance, User select s.inst_id, s.username, count(*) from gv$session s group by s.inst_id, s.username order by s.inst_id, s.username;

Locks (Holder/Waiter) for RAC column sess format A20 select substr(DECODE(request,0,'Holder: ','Waiter: ')||sid||' ('||inst_id||')',1,18) sess, id1, id2, lmode, request, type, to_char(to_date(ctime,'sssss'),' hh24:mi:ss') time_h_m_s from gv$lock where (id1, id2, type) IN (select id1, id2, type from gv$lock where request>0) order by id1, request, ctime desc;