select * from v$resource_limit;
Sunday, 5 February 2023
Fetching the data in excel sheet
set markup html on spool on
spool segment_tablespace.xls
select tablespace_name from dba_segments;
spool off
Objects in the database.
select owner,object-type,count(*) from dba_objects where owner not in (select username from dba_users where oracle_maintained='Y') group by owner,object_type order by 1;
select owner,object_type,count(*) from dba_objects where owner in ('HR') group by owner,object_type order by 1;
Scheduled jobs
SET LINESIZE 1000 PAGESIZE 1000
COLUMN log_user FORMAT A15
COLUMN priv_user FORMAT A15
COLUMN schema_user FORMAT A15
COLUMN interval FORMAT A40
COLUMN what FORMAT A50
COLUMN nls_env FORMAT A50
COLUMN misc_env FORMAT A50
SELECT a.job,
a.log_user,
a.priv_user,
a.schema_user,
To_Char(a.last_date,'DD-MON-YYYY HH24:MI:SS') AS last_date,
--To_Char(a.this_date,'DD-MON-YYYY HH24:MI:SS') AS this_date,
To_Char(a.next_date,'DD-MON-YYYY HH24:MI:SS') AS next_date,
a.broken,
a.interval,
a.failures,
a.what,
a.total_time,
a.nls_env,
a.misc_env
FROM dba_jobs a;
SET LINESIZE 80 PAGESIZE 14
Temp tablespace datafile
SET LINESIZE 200
COLUMN file_name FORMAT A70
SELECT file_id,
file_name,
ROUND(bytes/1024/1024/1024) AS size_gb,
ROUND(maxbytes/1024/1024/1024) AS max_size_gb,
autoextensible,
increment_by,
status
FROM dba_temp_files
ORDER BY file_name;
archive destination change
show parameter log_archive_dest_1
alter system set log_archive_dest_1='LOCATION=/temp/archivelog' scope=both
DB Link
###
on orcltarget db
conn apptarget/password
create database link orcl_source connect to appsource identified by password using 'orclsouceserver:1521/orcl';
select * from dba_db_links;
owner dblink username Host
apptarget link orcl_source appsource orclsouceserver:1521/orcl
Tablepsace and datafile command example
######### Tablespace size #####
https://www.oraask.com/2021/04/check-tablespace-usage-in-oracle.html
select t.tablespace, t.totalspace as " Totalspace(MB)",
round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",
nvl(fs.freespace,0) as "Freespace(MB)",
round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",
round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"
from
(select round(sum(d.bytes)/(1024*1024)) as totalspace,d.tablespace_name tablespace
from dba_data_files d
group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace,f.tablespace_name tablespace
from dba_free_space f
group by f.tablespace_name) fs
where t.tablespace=fs.tablespace (+)
order by "% Free";
#### Datafile ###
https://www.thegeekstuff.com/2016/10/oracle-tablespace-datafile/
SELECT file_id, file_name, bytes/1024/1024 size_mb, maxbytes, autoextensible,increment_by,status
FROM dba_data_files
WHERE tablespace_name = 'THEGEEKSTUFF';
#####
select name,state,total_mb,free_mb from v$asm_diskgroup;
alter tablespace tablespace_name add datafile '+DATA' size 100M autoextend on;
alter tablespace tablespace_name add datafile '+DATA' size 127G;
### SQL query returns the name of the tablespace that contains HR schema
select distinct tablespace_name from dba_segments where owner='HR';
##### To check Database total space.
select sum(bytes/1024/1024/1024) "Database Size(GB)" from dba_data_files;
###### Check Used space in DATABASE.
select sum(bytes/1024/1024/1024) "Database Used Space(GB)" from dba_segments;
##### To check Free space in DATABASE.
select sum(bytes/1024/1024/1024) "Database Free space(GB)" from dba_free_space;
#### How many datafile contains a Tablespace ###
select tablespace_name, file_name, status from dba_data_files order by 1;
select tablespace_name, count(*) data_file_count from dba_data_files group by tablespace_name;
##### Temp tablespace ####
Check the temp usage by sessions
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,
a.inst_id as Instance,
a.sid||','||a.serial# AS sid_serial,
NVL(a.username, '(oracle)') AS username,
a.program, a.status, a.sql_id
FROM gv$session a, gv$sort_usage b, gv$parameter p
WHERE p.name = 'db_block_size' AND a.saddr = b.session_addr
AND a.inst_id=b.inst_id AND a.inst_id=p.inst_id
ORDER BY temp_size desc;
Find the temp tablespace usage
select a.tablespace_name tablespace,
d.TEMP_TOTAL_MB,
sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_USED_MB,
d.TEMP_TOTAL_MB - sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_FREE_MB
from v$sort_segment a,
(
select b.name, c.block_size, sum (c.bytes) / 1024 / 1024 TEMP_TOTAL_MB
from v$tablespace b, v$tempfile c
where b.ts#= c.ts#
group by b.name, c.block_size
) d
where a.tablespace_name = d.name
group by a.tablespace_name, d.TEMP_TOTAL_MB;
Check detail of size and free space in TEMP
SELECT * FROM dba_temp_free_space;
Ref : https://smarttechways.com/2021/03/09/find-the-temp-usage-by-sessions-in-oracle/
##### Undo Tablespace #####
Check the undo tablespace total, free and used space(Size in MB) in Ora
SELECT a.tablespace_name,
SIZEMB,
USAGEMB,
(SIZEMB - USAGEMB) FREEMB
FROM ( SELECT SUM (bytes) / 1024 / 1024 SIZEMB, b.tablespace_name
FROM dba_data_files a, dba_tablespaces b
WHERE a.tablespace_name = b.tablespace_name AND b.contents like 'UNDO'
GROUP BY b.tablespace_name) a,
( SELECT c.tablespace_name, SUM (bytes) / 1024 / 1024 USAGEMB
FROM DBA_UNDO_EXTENTS c
WHERE status <> 'EXPIRED'
GROUP BY c.tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;
Check undo usage by User or schema
select u.tablespace_name tablespace, s.username, u.status, sum(u.bytes)/1024/1024 sum_in_mb, count(u.segment_name) seg_cnts
from dba_undo_extents u, v$transaction t , v$session s
where u.segment_name = '_SYSSMU' || t.xidusn || '$' and t.addr = s.taddr
group by u.tablespace_name, s.username, u.status order by 1,2,3;
Ref : https://smarttechways.com/2021/02/09/check-the-undo-tablespace-usage-in-oracle/
DDL
###### Tablespace ddl ############
Get the DDL of all the Tablespaces present in Oracle
set echo off;
Set pages 999;
set long 90000;
spool ddl_tablespace.sql
select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb;
spool off
Get the DDL for specific tablespace present in Oracle
set echo off;
Set pages 999;
set long 90000;
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','&TABLESPACE_NAME') FROM dual;
####### Table DDL #####
SET LONG 20000
set LONGCHUNKSIZE 20000
set PAGESIZE 0
set LINESIZE 1000
set FEEDBACK OFF
set VERIFY OFF
SELECT SELECT DBMS_METADATA.get_ddl ('TABLE', table_name, owner) FROM dual;
##### Table Partition #####
SET LONG 20000
set LONGCHUNKSIZE 20000
set PAGESIZE 0
set LINESIZE 1000
set FEEDBACK OFF
set VERIFY OFF
select DBMS_METADATA.GET_DDL('TABLE PARTITION','TRAN','IC') from dual;
###### Index DDL ####
set long 1000
set pagesize 0
select DBMS_METADATA.GET_DDL('INDEX','PERSON_IDX') from DUAL;
Table, Schema & Database size
1) Schema size
set linesize 150
set pagesize 5000
col owner for a15
col segment_name for a30
col segment_type for a20
col TABLESPACE_NAME for a30
clear breaks
clear computes
compute sum of SIZE_IN_GB on report
break on report
select OWNER,sum(bytes)/1024/1024/1000 “SIZE_IN_GB” from dba_segments group by owner order by 2;
select OWNER,sum(bytes)/1024/1024/1000 “SIZE_IN_GB” from dba_segments where owner='HR' group by owner;
Find Top Schemas by Size in Oracle
SELECT * FROM (
SELECT OWNER, SUM(BYTES)/1048576 MB
from DBA_SEGMENTS
GROUP BY OWNER
ORDER BY MB DESC)
WHERE ROWNUM < 20;
2) Table size
select segment_name,segment_type,round(SUM(BYTES)/power(2,20)) SEGMENT_SIZE_MB from dba_segments where segment_type='TABLE' and owner='TABLE_OWNER' and segment_name='<TABLE_NAME>';
Find Top Tables by Size in Oracle
SELECT * FROM (
SELECT SEGMENT_NAME, round(SUM(BYTES)/power(2,20)) SEGMENT_SIZE
FROM DBA_SEGMENTS where segment_type='TABLE'
GROUP BY SEGMENT_NAME
ORDER BY 2 desc)
WHERE ROWNUM < 101;
3) Database Size
select /*+parallel(4) */ round(sum(bytes / (1024*1024*1024))) "DB Size in GB" from dba_segments;
select /*+parallel(4) */ round(sum(bytes / (1024*1024*1024))) "DB Size in GB" from dba_data_files;
killing oracle session
1) killing session through shell script
sudo su - root
for proc in $(ps -ef|grep -i getsql|awk '{print $2}'); do print $proc; done
for proc in $(ps -ef|grep -i getsql|awk '{print $2}'); do kill $proc; done
1) ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
2) SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
SELECT s.inst_id,s.sid,s.serial#,s.sql_id,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';
3)SELECT s.sid, s.serial#,s.sql_id, p.spid
FROM v$process p, v$session s
WHERE p.addr = s.paddr
AND s.username = '<username>';
4)
set heading off
set line 1234
spool kill_01.sql
SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;'
FROM v$session
where username='YOUR_USER';
spool off;
set heading off
set line 1234
spool kill_01.sql
SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;'
FROM v$session
where sql_id='7d61bcjhkjh';
spool off;
5)
The UNIX Approach
% kill -9 spid
6)
In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.
ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';
v$session
Refer it contains all the sql related to v$session https://oracledbwr.com/check-oracle-active-and-inactive-sessions/
1)
Total Count of sessions
select count(s.status) TOTAL_SESSIONS
from gv$session s;
Total Count of Inactive sessions
select count(s.status) INACTIVE_SESSIONS
from gv$session s, v$process p
where
p.addr=s.paddr and
s.status=’INACTIVE’;
SESSIONS WHICH ARE IN INACTIVE STATUS FROM MORE THAN 1HOUR
select count(s.status) “INACTIVE SESSIONS > 1HOUR ”
from gv$session s, v$process p
where
p.addr=s.paddr and
s.last_call_et > 3600 and
s.status=’INACTIVE’;
COUNT OF ACTIVE SESSIONS
select count(s.status) ACTIVE_SESSIONS
from gv$session s, v$process p
where
p.addr=s.paddr and
s.status=’ACTIVE’;
col username for a25
set line 1234
select username, status, count(*) from v$session group by username,status order by username,status;
select username, status, machine, count(*) from v$session group by username,status, machine order by 1,2,3;
select sid,serial#,status,sql_id, status, machine from v$session where username='HR';
select sql_text from v$sql where sql_id='.........'
select sql_text from v$sqlarea where sql_id='.........'
select sql_text from v$sqltext where sql_id='.........'
select * from v$session_longops where time_remaining!=0;
SQL Gather stats job running since long time
##### Gather stats job running since long time ###
SELECT * FROM v$session_longops WHERE opname LIKE '%Gather%' AND time_remaining != 0 ORDER BY SID;
select sid, opname, target, sofar, totalwork,
units, to_char(start_time,'HH24:MI:SS') StartTime,
time_remaining, message, username
from v$session_longops
where sofar != totalwork
order by start_time
/
set lines 200
col OPNAME for a25
Select a.sid,a.serial#,b.status,a.opname,
to_char(a.START_TIME,' dd-Mon-YYYY HH24:mi:ss') START_TIME,
to_char(a.LAST_UPDATE_TIME,' dd-Mon-YYYY HH24:mi:ss') LAST_UPDATE_TIME,
a.time_remaining as "Time Remaining Sec" ,
a.time_remaining/60 as "Time Remaining Min",
a.time_remaining/60/60 as "Time Remaining HR"
From v$session_longops a, v$session b
where a.sid = b.sid
and a.sid =&sid
And time_remaining > 0;
https://www.support.dbagenesis.com/post/scheduling-jobs-with-dbms_scheduler
https://www.support.dbagenesis.com/post/gather-statistics-in-oracle
Friday, 3 February 2023
Export and import migration 12cr2 to 19c
Migration steps :
> Application shutdown - app team
DBA high level steps:
>Put tablespace in readonly
>take full export of source database (schemas)
>import full database from source to target
>startup application and test App team
1) Purge recyclebin on source
2) souce db : check if any user assighned system tablespaces
select username from dba_users where local_temp_tablespace='SYSTEM';
3) Put tablespace in read only mode
alter tablespace undotbs1 read only;
.alter tablespace temp read only;
alter tablespace sysaux read only;
alter tablespace hr_tbs read only.
select tablespace_name,status from dba_tablespaces;
select username from dba_users where oracle_maintained !='Y';
4) Run schemas export command
cat orclexp.par
dumpfile=orclexp%U.dmp
logfile=orclexp.log
directory=exp
exclude=statistics
parallel=4
schemas=
hr1
mr1
nohup expdp \''/ as sysdba\'' parfile=orclexp.par &
-------- OR -----
cat orclexp.par
userid="/as sysdba"
dumpfile=orclexp%U.dmp
logfile=orclexp.log
directory=exp
exclude=statistics
parallel=30
schemas=
hr1
mr1
nohup expdp parfile=orclexp.par &
5) export dblink and synonym
cat expdp_dblink_synonym.par
userid="/as sysdba"
directory=exp
dumpfile=expdp_dblink_synonym.dmp
logfile=expdp_dblink_synonym.log
include=DB_LINK,SYNONYM
full=Y
content=metadata_only
nohup expdp parfile=expdp_dblink_synonym.par &
6) After export lock the users
alter user hr1 lock;
alter user mr1 lock;
7) review both source and target db parameter files.
you can check it through pfile.
cat parametercheck.sql
set line 400
set pages 400
col name for a40
col value for a110
select name, value from v$parameter where name in ('sga_target','pga_aggregate_target')
processes
sessions
sga_target
undo_tablespace
audit_sys_operation
local_listener
audit_file_dest
audit_trail
db_name
db_unique_name
open_cursors
pga_aggregate_target
8) run the below command on source to get the create tablespace command for target.
select 'create tablespace '||tablespace_name||' datafile ''+DATA'' size '||ceil(sum(bytes/1024/1024/1024))||'g; ' from dba_data_files group by tablespace_name;
Note : here dest db datafile location is +DATA
---- or ---
set heading off;
set echo off;
set pages 999;
set long 90000;
spool ddl_list.sql
select dbms_metadata.get_ddl('TABLESPACE',tb.tablepspace_name) from dba_tablespaces tb;
9) create custom profiles
set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000
begin
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/
select dbms_metadata.get_ddl('PROFILE',PROFILE) as profile_ddl from (select distinct profile from dba_profiles);
10) check encryption on source db
select tablespace_name, encrypted from dba_tablespaces;
11) create roles -
set pages 400
select role from dba_roles;
12) take out grants from source and run on target before import =======
set heading off
set lines 4000 pages 4000
spool grants.sql
select 'grant '||privilege||' on "'||owner||'"."'||table_name||'" to "'||grantee||'" ;' from dba_tab_privs where grantee not in ('SYSDG','SYSKM','SYSBACKUP','SYSTEM','SYS','SYS$UMF','SYSRAC','OUTLIN','GSMADMIN_INTERNAL','XDB','DBSNMP','WMSYS','APPQOSSYS','GGSYS','DBSFWUSER,'ANONYMOUS','GSMCATUSER','ORACLE_OCM','DIP','GSMUSER','REMOTE_SCHEDULER_AGENT','XS$NULL','AUDSYS');
select 'grant '||privilege||' to "'||grantee||'" ;' from dba_sys_privs where grantee not in ('SYSDG','SYSKM','SYSBACKUP','SYSTEM','SYS','SYS$UMF','SYSRAC','OUTLIN','GSMADMIN_INTERNAL','XDB','DBSNMP','WMSYS','APPQOSSYS','GGSYS','DBSFWUSER,'ANONYMOUS','GSMCATUSER','ORACLE_OCM','DIP','GSMUSER','REMOTE_SCHEDULER_AGENT','XS$NULL','AUDSYS');
select 'grant '||granted_role||' to "'||grantee||'" ;' from dba_role_privs where grantee not in ('SYSDG','SYSKM','SYSBACKUP','SYSTEM','SYS','SYS$UMF','SYSRAC','OUTLIN','GSMADMIN_INTERNAL','XDB','DBSNMP','WMSYS','APPQOSSYS','GGSYS','DBSFWUSER,'ANONYMOUS','GSMCATUSER','ORACLE_OCM','DIP','GSMUSER','REMOTE_SCHEDULER_AGENT','XS$NULL','AUDSYS');
spool off
13) ***** ON Target *****
put target in noarchivelog mode
srvctl stop database -d orcl
srvctl start database -d orcl -o mount
alter database noarchivelog;
srvctl stop database -d orcl
srvctl start database -d orcl
14)
check no user exists before import.
select username from dba_users where oracle_maintained !='Y';
drop user username cascade;
15) Run schemas import command
cat orclimp.par
dumpfile=orclexp%U.dmp
logfile=orclimp.log
directory=exp
parallel=4
schemas=
hr1,
mr1
nohup expdp \''/ as sysdba\'' parfile=orclimp.par &
-------- OR -----
cat orclimp.par
userid="/as sysdba"
dumpfile=orclexp%U.dmp
logfile=orclimp.log
directory=exp
parallel=4
schemas=
hr1,
mr1
nohup impdp parfile=orclexp.par &
16) import dblink and synonym
cat impdp_dblink_synonym.par
userid="/as sysdba"
directory=exp
dumpfile=expdp_dblink_synonym.dmp
logfile=impdp_dblink_synonym.log
include=DB_LINK,SYNONYM
full=Y
content=metadata_only
nohup impdp parfile=impdp_dblink_synonym.par &
17) run utlrp
18) Post Migration check
match the object in source and target
select owner, object_type, count(*) from dba_objects where owner not in (select username from dba_users where oracle_maintained!='Y') group by owner, object_type order by 1;
19) compare public owned object between source and target.
20) Match that there are no extra invalid objects.
select owner, object_name, object_type from dba_objects where owner not in (select username from dba_users where oracle_maintained='Y') and status !='VALID' order by 1;
21) For invalid objects
select count(*) from dba_objects where status='INVALID';
select 'alter '||object_type||' '||owner||'.'||object_name||' compile;' from dba_objects where status!='VALID';
check the error.
select distinct owner, name, type, text, attribute, message_number from dba_errors where name='&object_name';
22) For DB link error
Set the global_name to false
compile the object
set the global_name to true
23) check database component and registry history
*** Source ***
col action_time for a35
col bundle_series for a15
col comments for a25
select action_time, action, bundle_series, comments from registry$history;
*****Target ***
col comp_name for a40
col version for a12
select comp_name, version, status from dba_registry;
col action_time for a35
col bundle_series for a15
col comments for a25
select action_time, action, bundle_series, comments from registry$history;
25) run stats gather
26) check sqlnet.ora
27) put solaris tablespaces in read only mode
28) inform app team ... send mail
29) put db in archive mode
srvctl stop database -d orcl
srvctl start database -d orcl -o mount
alter database archivelog;
srvctl stop database -d orcl
srvctl start database -d orcl
30) source database will remain in read only mode.
Subscribe to:
Comments (Atom)