Travellers Trail

Wednesday, 16 January 2019

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';

Wednesday, 17 October 2018

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;

Thursday, 4 October 2018

PDB DBA user


create user  c##dbareadonly  identified by  urdbapa55 container=all;

grant create session to c##dbateam container=all;
grant set container to c##dbateam container=all;
grant select_catalog_role to c##dbateam container=all;

alter user c##dbateam set container_data=all container = current;

Above (last line is important, otherwise dba user can't select * from v$pdbs, can't select from pdb views)

Favorite SQL for session waits

Session waits


col "FileId# Obj#" for a18
col event for a42

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

Other related, or common sql


-- Current Transaction
col "username_sid" for a35
select s.username||'('||s.sid||')' username_sid,s.sid,t.start_time,t.used_ublk as "Block Count", 
round(t.used_ublk*8192/1024/1024,2) Size_MB 
from v$transaction t,v$session s where s.saddr=t.ses_Addr order by start_time;


-- Long OPS
select sid, serial#, context, sofar, totalwork, target, sql_hash_value, 
round(sofar/totalwork*100,2) "%_COMPLETE", opname, to_char(start_time, 'DD Mon YYYY - HH24:MI') start_time, 
round(time_remaining/60) tr, to_char(last_update_time, 'DD Mon YYYY - HH24:MI') last_update_time 
from v$session_longops 
where totalwork != 0 and sofar <> totalwork 
order by start_time;

SQL from hash value, SQL hash


set line 200
select * from v$sql where hash_value = 9999999;

select sq.sql_text from v$sqltext_with_newlines sq 
where sq.hash_value = 9999999
order by sq.piece

SELECT s.username, sl.sid, sq.executions, sl.last_update_time, sl.sql_id, sl.sql_hash_value,
       opname, target, elapsed_seconds, time_remaining, sq.sql_fulltext
  FROM v$session_longops sl
 INNER JOIN v$sql sq ON sq.sql_id = sl.sql_id
 INNER JOIN v$session s ON sl.SID = s.SID AND sl.serial# = s.serial#
 WHERE time_remaining > 0

SQL from pid / session from Process Id / Session Id


set line 200
col schemaname for a20
col host_name for a20
col program for a16
col pprogram for a20
col machine for a20
col com for  a10

-- PID version
select p.spid, s.schemaname, s.sid, s.serial#, s.status, s.machine, s.program, 
   p.program pprogram, s.sql_hash_value, s.sql_id, s.prev_sql_id, 
   decode(command, 2, 'INSERT', 3, 'SELECT', 6, 'UPDATE', 7, 'DELETE', 47, 
   'PL/SQL EXE', command) com , i.host_name, i.instance_name 
from v$session s, v$process p, v$instance i
where s.paddr = p.addr 
   and p.spid = 22844;

-- SID version
col "Sid_Ser#_SqlHash"for a24
select p.spid, s.schemaname, s.sid||','||s.serial#||' ['||s.sql_hash_value||']' as "Sid_Ser#_SqlHash", s.status, 
   s.machine, s.program, p.program pprogram, s.sql_id, s.prev_sql_id, 
   decode(command, 2, 'INSERT', 3, 'SELECT', 6, 'UPDATE', 7, 'DELETE', 47, 
   'PL/SQL EXE', command) com , i.host_name, i.instance_name 
from v$session s, v$process p, v$instance i
where s.paddr = p.addr 
   and s.sid = 2790;

Wednesday, 12 September 2018

RMAN point in time restore challenge 12c

RMAN point in time pdb restore (using duplicate), gives error due issue with different pdb. Using Oracle 12.1.0.2.0 This is the restore command, I was using:

rman auxiliary / catalog rmanuser/password1@CATDB

run{
set UNTIL TIME "to_date('2018-07-15 00:00:01','YYYY-MM-DD HH24:MI:SS')" ;
DUPLICATE DATABASE 'proddb2' TO 'NI_REST' PLUGGABLE DATABASE CUSTX_PDB;
}

Error [1] faced, due to another pdb issue

Skipping pluggable database CUST_PDB5
Skipping pluggable database CUST_PDB6
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 09/06/2018 16:44:40
RMAN-05501: aborting duplication of target database
RMAN-06813: could not translate pluggable database STG_CUSTYPDB

Workaround, not using catalog

rman auxiliary / target sys/password2@proddb2

run{
set UNTIL TIME "to_date('2018-07-15 00:00:01','YYYY-MM-DD HH24:MI:SS')" ;
DUPLICATE DATABASE TO 'NI_REST' PLUGGABLE DATABASE CUSTX_PDB;
}

Error [2] now faced, due to missing arc

Executing: alter database datafile 832 offline drop
Executing: alter database datafile 833 offline drop
Executing: alter database datafile 834 offline drop
starting media recovery

unable to find archived log
archived log thread=1 sequence=13054
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 3 needs more recovery to be consistent
ORA-01110: data file 3: '+DATA/NI_REST/DATAFILE/sysaux.675.986210261'

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 09/07/2018 13:24:29
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 13054 and starting SCN of 7885151161

RMAN>

Workaround

Restored around 15 archive log, starting from above sequence (13054, also for thread 2)
Redone the RMAN clone command
Opening db gave error with control file
Create backup control file, used RESETLOGS block

Removed all non NI_REST datafiles
Kept only 3 LOGFILE, they do not exits on filesystem

Run the backup control (notice has parent db name (PRODDB2) and resetlogs)
Start db with open resetlog
Opened the pdb successfully
Recreated the temp file

With the above, restore was successful. exported the schema from pdb and aux db dropped.

Appendix:


catalog start with /u01/oracle/product/12102_01/dbs/' noprompt;


alter database backup controlfile to trace as '/home/oracle/mydb_new.ctl.txt';


STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "PRODDB2" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 584
LOGFILE
  GROUP 1 '+DATA/NI_REST/ONLINELOG/group_1.262.903320613' SIZE 1024M BLOCKSIZE 512,
  GROUP 2 '+DATA/NI_REST/ONLINELOG/group_2.263.903320623' SIZE 1024M BLOCKSIZE 512,
  GROUP 3 '+DATA/NI_REST/ONLINELOG/group_3.269.903321099' SIZE 1024M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/NI_REST/DATAFILE/system.693.986210261',
  '+DATA/NI_REST/DATAFILE/sysaux.675.986210261',
  '+DATA/NI_REST/DATAFILE/undotbs1.683.986210259',
  '+DATA/NI_REST/2B4E02B966694E2CE0533F1C6F0A31F5/DATAFILE/system.461.986212971',
  '+DATA/NI_REST/2B4E02B966694E2CE0533F1C6F0A31F5/DATAFILE/sysaux.467.986212969',
  '+DATA/NI_REST/DATAFILE/undotbs2.474.986213075',
  '+DATA/NI_REST/2DB32189A8D41995E0533F1C6F0A911E/DATAFILE/system.459.986212545',
  '+DATA/NI_REST/2DB32189A8D41995E0533F1C6F0A911E/DATAFILE/sysaux.460.986212545',
  '+DATA/NI_REST/2DB32189A8D41995E0533F1C6F0A911E/DATAFILE/default_pdb_ts.611.986212545',
  '+DATA/NI_REST/2DB32189A8D41995E0533F1C6F0A911E/DATAFILE/users.561.986213451'
CHARACTER SET AL32UTF8
;


alter database OPEN RESETLOGS;


alter tablespace TEMP add tempfie '+DATA' size 150m;

Appendix: error


SQL> alter database open resetlogs;
alter database open resetlogs 
* 
ERROR at line 1: 
ORA-19838: Cannot use this control file to open database

Thursday, 21 June 2018

PDB Tips

List pdbs

show pdbs

select * from v$pdbs;

# All PDB (inc. size)
col name for a30
select name, open_mode, total_size/1024/1024 as size_mb  from v$pdbs where name like '%';

PDB sessions, pdb connections, pdb sess

col name for a20
col username for a32
col machine for a20

select s.inst_id, p.name,  s.username, s.machine, count(*) as "SESSION COUNT"
from gv$session s, gv$pdbs p 
where p.con_id = s.con_id and s.inst_id = p.inst_id 
group by s.inst_id, p.name, s.username, s.machine order by p.name, s.machine;


-- List only STG_BIZ_X
select s.inst_id, p.name,  s.username, s.machine, s.sid, s.serial#, s.status
from gv$session s, gv$pdbs p 
where p.con_id = s.con_id and s.inst_id = p.inst_id 
and p.name = 'STG_BIZ_X';


INST_ID NAME          USERNAME      MACHINE      SID       SERIAL# STATUS
------- ------------- ------------- ------------ --------- ------- --------
  1     STG_BIZ_X     SYS           ukdevdb017     442     28213   ACTIVE
  1     STG_BIZ_X     C##JPTADMIN   ukdevdb602     777     56244   KILLED
  2     STG_BIZ_X     C##JPTADMIN   ukdevdb601      66     64486   KILLED
  2     STG_BIZ_X     SYS           ukdevdb017    1959     13580   ACTIVE
  2     STG_BIZ_X     C##JPTADMIN   ukdevdb602    1961     46827   KILLED

Grid installation prerequisites

Getting error

[grid@proddev1 ~]$ cd /u01/app/grid/12201
[grid@proddev1 12201]$ ./runcluvfy.sh stage -pre crsinst -n proddev1,proddev2 -fixup -verbose

ERROR:
PRVG-10467 : The default Oracle Inventory group could not be determined.

Verifying '/tmp/' ...FAILED (PRVG-1901)

Pre-check for cluster services setup was unsuccessful on all the nodes.


Failures were encountered during execution of CVU verification request "stage -pre crsinst".

Verifying '/tmp/' ...FAILED
PRVG-1901 : failed to setup CVU remote execution framework directory
"/tmp/CVU_12.2.0.1.0_grid/" on nodes "proddev1"

trprdbr04017: Version of exectask could not be retrieved from node
"trprdbr04017"
trprdbr04017: Version of exectask could not be retrieved from node
"trprdbr04017"


CVU operation performed:      stage -pre crsinst
Date:                         Jun 21, 2018 9:37:11 AM
CVU home:                     /u01/app/grid/12201/
User:                         grid

FIX: Correct the fstab, to make sure, its like below exactly

[root@proddev1 ~]# more /etc/fstab |grep -E "/tmp | /opt"
/dev/mapper/vg_apol-lv_tmp /tmp                    ext3    defaults,nodiratime,nodev,nosuid 1 2
/dev/mapper/vg_otc0-lv_opt /opt                    ext3    defaults,nodiratime,nodev 1 2