Hi all Hope definitely below queries is very useful to health check the
database.
1. To check sync status of the database
02. To check active and inactive session details with sqlid, sid and machine.....
#active_sessions_details
Active :
Inactive :
03. To check Depencies of the objects
04. To check oracle_home path ,instead of checking oratab in cat /etc/oratab
cd /var/opt/oracle/
cat oratab
05. To check query is forwarding or not in database.
06. To check temp tablespace how much space is consumed.
select name,db_unique_name,TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024/1024 as tbs_size,FREE_SPACE/1024/1024/1024 as tbs_free,round(((TABLESPACE_SIZE/1024/1024/1024)-(FREE_SPACE/1024/1024/1024))/(TABLESPACE_SIZE/1024/1024/1024),2)*100 as pct_full
from dba_temp_free_space a,v$instance,v$database;
07. To refresh mview (materialized view)
select name,database_role,log_mode,open_mode from v$database;
execute DBMS_MVIEW.REFRESH('SCHEMA_NAME.MV_NAME','C');
08. To check when last mv is refresh happens
alter session set nls_date_format='DD-MON-YYYY HH2:MI:SS';
select OWNER,MVIEW_NAME,last_refresh_date from all_mviews where owner='' and mview_name in ('');
09. To take ORACLE HOME backup
tar -cvf ORACLE_HOME_BKP1.tar /orahome_slm/app/oracle/product/11.1.0.2/dbhome_3
10. To check oracle NO process related in os level
ps -ef|grep LOCAL=NO|grep -i DB_NAME
11. Delete archives without asking yes/no option (like prompt)
run
{
crosscheck archivelog all;
delete force noprompt archivelog all completed before 'sysdate-2';
delete force noprompt expired archivelog all;
}
12. Delete archives with prompt asking yes/no option
delete archivelog until time 'sysdate-2';
13. To delete archives for force fully.
delete force archivelog completed before 'sysdate-1';
14. To check particular archives available
list archivelog sequence ( no ) -------check archive exits or not
15. Connect DC to DR or DR to DC,using with service name
checkt the fal server name
show parameter fal_server
conn sys/password@targetservicename as sysdba
16. To Create user profile and assign to profile
SQL> create profile temp_profile limit password_reuse_max unlimited password_reuse_time unlimited;
Profile created.
SQL> alter user SCHEMA_NAME profile temp_profile;
17. Mrp start and stop command
Start:
alter database recover managed standby database disconnect from session;
Stop:
alter database recover managed standby database disconnect cancel;
18. To connect pl/sql developer software without tns name
Database -- scan_name:scan_port/service_name
Database -- servicename or ip:port_number/dbname
19. To check Ports numbers
Note : This is based on environment ,below ports is just exmaples :
show parameter remote_listener
srvctl config scan --> to find scan port and scan IP
srvctl config nodeapps -a --> to find virtual port and IP
FOR SCAN IP :1651
FOR Physical IP and Virtual IP : 1621
20. To check port open or not
telnet IP port_number --> command
which port we have to give scan port or vip port -------for SCAN ip give 1651 ( Note :Based on environment )
for vip and pysical is 1621 ( Note :Based on environment )
srvctl config scan --> to check scan name and ip
srvctl config scan_listener --> to check scan port
srvctl config nodeapps -a ---> to check vip name and IP
srvctl config listener or show parameter remote_listener --> to check vip port
\
21. To deleting the trace files,trim files and audti files in backup ground
nohup find . -name '*.trc' -mtime +1 -exec rm -r {} \; &
nohup find . -name '*.trm' -mtime +4 -exec rm -r {} \; &
nohup find . -name '*.aud' -mtime +3 -exec rm -r {} \; &
22. kill at os level related to database related
Note : don't use without proper knowing the output
ps -ef|grep LOCAL=NO|grep -i DBNAME|awk '{print "kill -9 "$2}' > kill.sh
23. To kill particular session in database level
Syntax : alter system kill session 'SID,SERIAL#,@INSTANCENUMBER' immediate;
Ex:
alter system kill session '324,7879,@2' immediate;
24. To find stale stats and last analyzed for particular schema
stale --> yes means we have to run stats gather,no means no need.
select
table_name,
stale_stats,
last_analyzed
from
dba_tab_statistics
where
owner ='schema_name'
order by
last_analyzed desc, table_name asc;
25. To display (Dynamic query) grants from user to user for tables
SQL> select 'grant '||' '||'select'||' '||'on '||' '||'FROMUSER'||'.'||table_name ||' '||'to TOUSER;' from dba_tables where owner='FROMUSER';
26. To check unix mount point it will shows top consumed in top.
$du -sm *|sort -nr
27. To Take export meta data backup
expdp directory=EXP_SCHEMA_date dumpfile=EXP_schema_metadata_date_%U.dmp logfile=EXP_schema_metadata_date.log schemas=Schemaname content=metadata_only compression=all cluster=n metrics=y
28. To view first ten files
ls -al|head 11
29. ASM disk space check
set lines 150 pages 200
select name,type,total_mb/1024,free_mb/1024,USABLE_FILE_MB/1024 from v$asm_diskgroup;
30. To check Tablescape space in database level.
col TABLESPACE_NAME for a30
REM Tablespace & freespace
set linesize 150
set pages 10000
SELECT tablespace_name,ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB,
ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,
ROUND(SUM(max_mb)) MAX_SZ_MB,
ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL
FROM (
SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,
0 TOTAL_MB, 0 MAX_MB
FROM dba_free_space
GROUP BY tablespace_name
UNION
SELECT tablespace_name, 0 CURRENT_MB,
SUM(bytes)/1024/1024 TOTAL_MB,
SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
FROM dba_data_files
GROUP BY tablespace_name)
GROUP BY tablespace_name
order by 5;
31. To check default temp tablespace
SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE'
32. To check top 20 consumed tablespace detail
SELECT grantee, privilege FROM dba_sys_privs where privilege like '%UNLIMITED%TABLE%' and grantee in (select username from dba_users where account_status='OPEN'and username not in ('SYS','SYSTEM','SYSMAN','GGS','DBSNMP','DBMON','DBSPI','OUTLN'));
33. To check datafile for particular tablespace releated
set line 160
set pagesize 500
col name for a70
col TABLESPACE_NAME for a25
select df.NAME,df.TABLESPACE_NAME,round(df.BYTES/1024/1024) Allocated,
round(nvl(dfs.BYTES/1024/1024,0)) FREE_SPACE, round(((df.BYTES/1024/1024)-nvl((dfs.BYTES/1024/1024),0))) USED_SPACE
from v$datafile_header df,
(select file_id,tablespace_name,sum(bytes) bytes
from dba_free_space
group by file_id,tablespace_name) dfs
where df.FILE#=dfs.FILE_ID (+)
AND df.TABLESPACE_NAME like upper('&Tablespace')
order by CREATION_TIME,free_space;
34. To Rebuild index using below command.
alter index schemaname.index_name rebuild online;
35. Rebuild index parallel (online) and no parallel (offline)
alter index USERNAME.INDEX_NAME rebuild parallel 8;
alter index USERNAME.INDEX_NAME rebuild online noparallel;
36. To compile invalid objects
Ex:
alter materialized view schemaname.MV_Name compile;
37. To Check RMAN Running backup in database
rman bkp status
Set line 2000
col end_time for a20
col start_time for a20
col time_taken_display for a20
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
time_taken_display,
input_bytes/1024/1024/1024,
output_bytes/1024/1024/1024
from V$RMAN_BACKUP_JOB_DETAILS
order by start_time;
38. To check the list of Privileges granted to a User (Grantee) :
Note : just for understanding :grantee --- means privilege taken and grantor privilege given
col Grantor for a12
col Grantee for a12
col Table_name for a12
set line 120
select GRANTEE, OWNER "OBJECT_OWNER", GRANTOR, TABLE_NAME, PRIVILEGE from DBA_TAB_PRIVS where GRANTEE='Schema_name';
39. To check the list of Roles granted to a User (Grantee) :
col Granted_role for a12
col Grantee for a12
set line 120
select GRANTEE, GRANTED_ROLE, ADMIN_OPTION from DBA_ROLE_PRIVS where GRANTEE=' ';
40: To check the System Privileges granted to a particular Role:
col Role for a12
set line 80
select ROLE, PRIVILEGE, ADMIN_OPTION from ROLE_SYS_PRIVS where ROLE=' ';
41. To check the Object Privileges granted to a particular Role:
col Role for a12
col Owner for a12
col Table_name for a12
col Column_name for a12
set line 120
select ROLE, OWNER "OBJECT_OWNER", TABLE_NAME, COLUMN_NAME, PRIVILEGE, GRANTABLE from ROLE_TAB_PRIVS where ROLE=' ';
42. To create DB link (DATABASE LINK)
conn Schema_name/Schema_name_pwd
create database link <dblink_name> connect to user identified by <user password> using '<database name>';
conn Schema_name/Schema_name#143
create database link DB_LINK_NAME connect to SCHEMA_NAME identified by pwd using 'service_name';
43. To check index ,related to particular schema and particular table.
select owner,segment_type,segment_name,sum(bytes)/1024/1024/1024 from dba_segments where segment_name in (select index_name from dba_indexes where table_name='' and table_owner='USERNAME') group by owner,segment_type,segment_name
order by owner,segment_type,segment_name;
44. To Creating new user/schema
CREATE USER Schema_name IDENTIFIED BY passpwd DEFAULT TABLESPACE tablespace_name TEMPORARY TABLESPACE TEMP;
GRANT connect TO Schema_name ;
GRANT RESOURCE TO Schema_name ;
45. To check owner of object
select object_name,object_type,owner from dba_objects where object_name=' ';
46. Fragmentation check query
set lines 9999
set pages 2000
select owner,table_name,
round(((blocks*8)/1024),2) "object size mb",
round((num_rows*avg_row_len/1024/1024),2) "actual data in table mb",
round(((blocks*8)/1024),2)- round((num_rows*avg_row_len/1024/1024),2) "wasted space mb",
((round(((blocks*8)/1024),2)- round((num_rows*avg_row_len/1024/1024),2)) / (round(((blocks*8)/1024),2)) ) *100 "percent fragmented"
from dba_tables
where blocks != 0
and owner = '&OWNER'
order by 1,4 desc
/
47. check holding and waiting session :
set lines 200
col sess for a25
select inst_id,decode(request,0,'holding_slm: ','waiting_slm: ')|| sid sess,id1,id2,lmode, request, type from gv$lock
where (id1,id2,type)in(select id1,id2,type from gv$lock where request>0)
order by id1,request;
48. How to run schema stats gather
EXEC DBMS_STATS.gather_schema_stats('SCHEMA_NAME',degree=>4,cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
49. To run Table stats gather
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCHEMANAME',TABNAME =>'TABLE_NAME',degree=> 8, CASCADE=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
50. To check Particular running Job Details
col owner format a10
col job_name format a30
col START_DATE format a35
set lines 200 pages 200
select owner,JOB_NAME,START_DATE,NEXT_RUN_DATE from all_scheduler_jobs where job_name in ('JOB_NAME'');
Part2:Below details is useful very most :
1. To check sync status of the database
select name,instance_name,open_mode,database_role from
v$database,v$instance;
select thread#,max(sequence#) from v$archived_log a,v$database b where
a.resetlogs_change#=b.resetlogs_change# group by thread# order by thread#;
select thread#,max(sequence#) from v$log_history a,v$database b where
a.resetlogs_change#=b.resetlogs_change# group by thread# order by thread#;
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select distinct(checkpoint_time) from v$datafile_header;
02. To check active and inactive session details with sqlid, sid and machine.....
#active_sessions_details
Active :
col status for a9
col event for a30
col username for a10
col module for a25
col machine for a15
col osuser for a10
set lines 200 pages 50000
select username,inst_id, sid, command,serial#, sql_id,osuser,
row_wait_obj#,machine ,event, module,
round(last_call_et) LCE from gv$session s where username is not null
and status='ACTIVE'
order by inst_id, event, sql_id
/
Inactive :
col status for a9
col event for a30
col username for a10
col module for a25
col machine for a15
col osuser for a10
set lines 200 pages 50000
select inst_id, sid,username, command,serial#, sql_id,osuser,
row_wait_obj#,machine ,event, module,
round(last_call_et) LCE from gv$session s where username is not null
and status='INACTIVE'
order by inst_id, event, sql_id
/
03. To check Depencies of the objects
col owner format a20
col NAME format a20
col REFERENCED_OWNER format a30
col REFERENCED_NAME format a30
col REFERENCED_LINK_NAME format a30
set lines 200 pages 200
select owner,name,type,REFERENCED_OWNER,REFERENCED_NAME,REFERENCED_TYPE from
dba_dependencies where owner='SCHEMA_NAME';
04. To check oracle_home path ,instead of checking oratab in cat /etc/oratab
cd /var/opt/oracle/
cat oratab
05. To check query is forwarding or not in database.
Select SID,BLOCK_GETS,CONSISTENT_GETS,PHYSICAL_READS,BLOCK_CHANGES, OPTIMIZED_PHYSICAL_READS from gv$sess_io where SID='143';
06. To check temp tablespace how much space is consumed.
select name,db_unique_name,TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024/1024 as tbs_size,FREE_SPACE/1024/1024/1024 as tbs_free,round(((TABLESPACE_SIZE/1024/1024/1024)-(FREE_SPACE/1024/1024/1024))/(TABLESPACE_SIZE/1024/1024/1024),2)*100 as pct_full
from dba_temp_free_space a,v$instance,v$database;
07. To refresh mview (materialized view)
select name,database_role,log_mode,open_mode from v$database;
execute DBMS_MVIEW.REFRESH('SCHEMA_NAME.MV_NAME','C');
08. To check when last mv is refresh happens
alter session set nls_date_format='DD-MON-YYYY HH2:MI:SS';
select OWNER,MVIEW_NAME,last_refresh_date from all_mviews where owner='' and mview_name in ('');
09. To take ORACLE HOME backup
tar -cvf ORACLE_HOME_BKP1.tar /orahome_slm/app/oracle/product/11.1.0.2/dbhome_3
10. To check oracle NO process related in os level
ps -ef|grep LOCAL=NO|grep -i DB_NAME
11. Delete archives without asking yes/no option (like prompt)
run
{
crosscheck archivelog all;
delete force noprompt archivelog all completed before 'sysdate-2';
delete force noprompt expired archivelog all;
}
12. Delete archives with prompt asking yes/no option
delete archivelog until time 'sysdate-2';
13. To delete archives for force fully.
delete force archivelog completed before 'sysdate-1';
14. To check particular archives available
list archivelog sequence ( no ) -------check archive exits or not
15. Connect DC to DR or DR to DC,using with service name
checkt the fal server name
show parameter fal_server
conn sys/password@targetservicename as sysdba
16. To Create user profile and assign to profile
SQL> create profile temp_profile limit password_reuse_max unlimited password_reuse_time unlimited;
Profile created.
SQL> alter user SCHEMA_NAME profile temp_profile;
17. Mrp start and stop command
Start:
alter database recover managed standby database disconnect from session;
Stop:
alter database recover managed standby database disconnect cancel;
18. To connect pl/sql developer software without tns name
Database -- scan_name:scan_port/service_name
Database -- servicename or ip:port_number/dbname
19. To check Ports numbers
Note : This is based on environment ,below ports is just exmaples :
show parameter remote_listener
srvctl config scan --> to find scan port and scan IP
srvctl config nodeapps -a --> to find virtual port and IP
FOR SCAN IP :1651
FOR Physical IP and Virtual IP : 1621
20. To check port open or not
telnet IP port_number --> command
which port we have to give scan port or vip port -------for SCAN ip give 1651 ( Note :Based on environment )
for vip and pysical is 1621 ( Note :Based on environment )
srvctl config scan --> to check scan name and ip
srvctl config scan_listener --> to check scan port
srvctl config nodeapps -a ---> to check vip name and IP
srvctl config listener or show parameter remote_listener --> to check vip port
\
21. To deleting the trace files,trim files and audti files in backup ground
nohup find . -name '*.trc' -mtime +1 -exec rm -r {} \; &
nohup find . -name '*.trm' -mtime +4 -exec rm -r {} \; &
nohup find . -name '*.aud' -mtime +3 -exec rm -r {} \; &
22. kill at os level related to database related
Note : don't use without proper knowing the output
ps -ef|grep LOCAL=NO|grep -i DBNAME|awk '{print "kill -9 "$2}' > kill.sh
23. To kill particular session in database level
Syntax : alter system kill session 'SID,SERIAL#,@INSTANCENUMBER' immediate;
Ex:
alter system kill session '324,7879,@2' immediate;
24. To find stale stats and last analyzed for particular schema
stale --> yes means we have to run stats gather,no means no need.
select
table_name,
stale_stats,
last_analyzed
from
dba_tab_statistics
where
owner ='schema_name'
order by
last_analyzed desc, table_name asc;
25. To display (Dynamic query) grants from user to user for tables
SQL> select 'grant '||' '||'select'||' '||'on '||' '||'FROMUSER'||'.'||table_name ||' '||'to TOUSER;' from dba_tables where owner='FROMUSER';
26. To check unix mount point it will shows top consumed in top.
$du -sm *|sort -nr
27. To Take export meta data backup
expdp directory=EXP_SCHEMA_date dumpfile=EXP_schema_metadata_date_%U.dmp logfile=EXP_schema_metadata_date.log schemas=Schemaname content=metadata_only compression=all cluster=n metrics=y
28. To view first ten files
ls -al|head 11
29. ASM disk space check
set lines 150 pages 200
select name,type,total_mb/1024,free_mb/1024,USABLE_FILE_MB/1024 from v$asm_diskgroup;
30. To check Tablescape space in database level.
col TABLESPACE_NAME for a30
REM Tablespace & freespace
set linesize 150
set pages 10000
SELECT tablespace_name,ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB,
ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,
ROUND(SUM(max_mb)) MAX_SZ_MB,
ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL
FROM (
SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,
0 TOTAL_MB, 0 MAX_MB
FROM dba_free_space
GROUP BY tablespace_name
UNION
SELECT tablespace_name, 0 CURRENT_MB,
SUM(bytes)/1024/1024 TOTAL_MB,
SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
FROM dba_data_files
GROUP BY tablespace_name)
GROUP BY tablespace_name
order by 5;
31. To check default temp tablespace
SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE'
32. To check top 20 consumed tablespace detail
SELECT grantee, privilege FROM dba_sys_privs where privilege like '%UNLIMITED%TABLE%' and grantee in (select username from dba_users where account_status='OPEN'and username not in ('SYS','SYSTEM','SYSMAN','GGS','DBSNMP','DBMON','DBSPI','OUTLN'));
33. To check datafile for particular tablespace releated
set line 160
set pagesize 500
col name for a70
col TABLESPACE_NAME for a25
select df.NAME,df.TABLESPACE_NAME,round(df.BYTES/1024/1024) Allocated,
round(nvl(dfs.BYTES/1024/1024,0)) FREE_SPACE, round(((df.BYTES/1024/1024)-nvl((dfs.BYTES/1024/1024),0))) USED_SPACE
from v$datafile_header df,
(select file_id,tablespace_name,sum(bytes) bytes
from dba_free_space
group by file_id,tablespace_name) dfs
where df.FILE#=dfs.FILE_ID (+)
AND df.TABLESPACE_NAME like upper('&Tablespace')
order by CREATION_TIME,free_space;
34. To Rebuild index using below command.
alter index schemaname.index_name rebuild online;
35. Rebuild index parallel (online) and no parallel (offline)
alter index USERNAME.INDEX_NAME rebuild parallel 8;
alter index USERNAME.INDEX_NAME rebuild online noparallel;
36. To compile invalid objects
Ex:
alter materialized view schemaname.MV_Name compile;
37. To Check RMAN Running backup in database
rman bkp status
Set line 2000
col end_time for a20
col start_time for a20
col time_taken_display for a20
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
time_taken_display,
input_bytes/1024/1024/1024,
output_bytes/1024/1024/1024
from V$RMAN_BACKUP_JOB_DETAILS
order by start_time;
38. To check the list of Privileges granted to a User (Grantee) :
Note : just for understanding :grantee --- means privilege taken and grantor privilege given
col Grantor for a12
col Grantee for a12
col Table_name for a12
set line 120
select GRANTEE, OWNER "OBJECT_OWNER", GRANTOR, TABLE_NAME, PRIVILEGE from DBA_TAB_PRIVS where GRANTEE='Schema_name';
39. To check the list of Roles granted to a User (Grantee) :
col Granted_role for a12
col Grantee for a12
set line 120
select GRANTEE, GRANTED_ROLE, ADMIN_OPTION from DBA_ROLE_PRIVS where GRANTEE=' ';
40: To check the System Privileges granted to a particular Role:
col Role for a12
set line 80
select ROLE, PRIVILEGE, ADMIN_OPTION from ROLE_SYS_PRIVS where ROLE=' ';
41. To check the Object Privileges granted to a particular Role:
col Role for a12
col Owner for a12
col Table_name for a12
col Column_name for a12
set line 120
select ROLE, OWNER "OBJECT_OWNER", TABLE_NAME, COLUMN_NAME, PRIVILEGE, GRANTABLE from ROLE_TAB_PRIVS where ROLE=' ';
42. To create DB link (DATABASE LINK)
conn Schema_name/Schema_name_pwd
create database link <dblink_name> connect to user identified by <user password> using '<database name>';
conn Schema_name/Schema_name#143
create database link DB_LINK_NAME connect to SCHEMA_NAME identified by pwd using 'service_name';
43. To check index ,related to particular schema and particular table.
select owner,segment_type,segment_name,sum(bytes)/1024/1024/1024 from dba_segments where segment_name in (select index_name from dba_indexes where table_name='' and table_owner='USERNAME') group by owner,segment_type,segment_name
order by owner,segment_type,segment_name;
44. To Creating new user/schema
CREATE USER Schema_name IDENTIFIED BY passpwd DEFAULT TABLESPACE tablespace_name TEMPORARY TABLESPACE TEMP;
GRANT connect TO Schema_name ;
GRANT RESOURCE TO Schema_name ;
45. To check owner of object
select object_name,object_type,owner from dba_objects where object_name=' ';
46. Fragmentation check query
set lines 9999
set pages 2000
select owner,table_name,
round(((blocks*8)/1024),2) "object size mb",
round((num_rows*avg_row_len/1024/1024),2) "actual data in table mb",
round(((blocks*8)/1024),2)- round((num_rows*avg_row_len/1024/1024),2) "wasted space mb",
((round(((blocks*8)/1024),2)- round((num_rows*avg_row_len/1024/1024),2)) / (round(((blocks*8)/1024),2)) ) *100 "percent fragmented"
from dba_tables
where blocks != 0
and owner = '&OWNER'
order by 1,4 desc
/
47. check holding and waiting session :
set lines 200
col sess for a25
select inst_id,decode(request,0,'holding_slm: ','waiting_slm: ')|| sid sess,id1,id2,lmode, request, type from gv$lock
where (id1,id2,type)in(select id1,id2,type from gv$lock where request>0)
order by id1,request;
48. How to run schema stats gather
EXEC DBMS_STATS.gather_schema_stats('SCHEMA_NAME',degree=>4,cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
49. To run Table stats gather
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCHEMANAME',TABNAME =>'TABLE_NAME',degree=> 8, CASCADE=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
50. To check Particular running Job Details
col owner format a10
col job_name format a30
col START_DATE format a35
set lines 200 pages 200
select owner,JOB_NAME,START_DATE,NEXT_RUN_DATE from all_scheduler_jobs where job_name in ('JOB_NAME'');
Part2:Below details is useful very most :
1. To check sync status of the database
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select thread#,max(sequence#) from v$archived_log a,v$database b where a.resetlogs_change#=b.resetlogs_change# group by thread# order by thread#;
select thread#,max(sequence#) from v$log_history a,v$database b where a.resetlogs_change#=b.resetlogs_change# group by thread# order by thread#;
select distinct(checkpoint_time) from v$datafile_header;
02. To check active and inactive session details with sqlid, sid and machine.....
@sessions
Active :
col status for a9
col event for a30
col username for a10
col module for a25
col machine for a15
col osuser for a10
set lines 200 pages 50000
select inst_id, sid, serial#, sql_id,osuser, username, row_wait_obj#, event, module,machine,
round(last_call_et) LCE from gv$session s where username is not null
and status='ACTIVE'
order by inst_id, event, sql_id
/
Inactive :
col status for a9
col event for a30
col username for a10
col module for a25
col machine for a15
col osuser for a10
set lines 200 pages 50000
select inst_id, sid, serial#, sql_id,osuser, username, row_wait_obj#, event, module,machine,
round(last_call_et) LCE from gv$session s where username is not null
and status='INACTIVE'
order by inst_id, event, sql_id
/
03. To Check Long running session details in Database
@long_running_sessions
set lines 200 pages 8000
col serial# for 9999999
col machine for a20
col progress_pct for 99999999.00
col elapsed for a20
col remaining for a20
col opname for a30
SELECT s.inst_id,sl.opname,
s.sid,
s.serial#,s.status,
s.machine,sl.opname,
trunc(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
trunc(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining
FROM gv$session s,
gv$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial#
and sl.time_remaining>0
order by inst_id;
04. To check Particular session with query details,how running it's running the query.
@querydetails_check
set echo off
set serveroutput on size 50000
set verify off
set feedback off
accept inst_id prompt 'Enter instance id: '
accept SID prompt 'Enter sid: '
DECLARE
v_sid number;
vs_cnt number;
s sys.gv_$session%ROWTYPE;
p sys.gv_$process%ROWTYPE;
cursor cur_c1 is select sid from sys.gv_$process p,sys.gv_$session s where p.addr = s.paddr and sid = &SID and s.inst_id='&inst_id' and p.inst_id=s.inst_id;
BEGIN
dbms_output.put_line('=====================================================================');
select nvl(count(sid),0) into vs_cnt from sys.gv_$process p, sys.gv_$session s where p.addr = s.paddr and sid = &SID and s.inst_id='&inst_id' and p.inst_id=s.inst_id;
open cur_c1;
LOOP
FETCH cur_c1 INTO v_sid;
EXIT WHEN (cur_c1%NOTFOUND);
select * into s from sys.gv_$session where sid = v_sid and inst_id='&inst_id';
select * into p from sys.gv_$process where addr = s.paddr and inst_id='&inst_id';
dbms_output.put_line('INST_ID : '|| s.inst_id);
dbms_output.put_line('SID/Serial : '|| s.sid||','||s.serial#);
dbms_output.put_line('Foreground : '|| 'PID: '||s.process||' - '||s.program);
dbms_output.put_line('Shadow : '|| 'PID: '||p.spid||' - '||p.program);
dbms_output.put_line('Terminal : '|| s.terminal || '/ ' || p.terminal);
dbms_output.put_line('OS User : '|| s.osuser||' on '||s.machine);
dbms_output.put_line('Ora User : '|| s.username);
dbms_output.put_line('Details : '|| s.action||' - '||s.module);
dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Login Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
dbms_output.put_line('Last Call : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '9990.0') || ' min');
dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
dbms_output.put_line('Latch Spin : '|| nvl(p.latchspin, 'NONE'));
dbms_output.put_line('Current SQL statement:');
for c1 in ( select * from sys.gv_$sqltext where HASH_VALUE = s.sql_hash_value and inst_id='&inst_id' order by piece)
loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Previous SQL statement:');
for c1 in ( select * from sys.gv_$sqltext where HASH_VALUE = s.prev_hash_value and inst_id='&inst_id' order by piece)
loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Session Waits:');
for c1 in ( select * from sys.gv_$session_wait where sid = s.sid and inst_id='&inst_id')
loop
dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
end loop;
dbms_output.put_line('Connect Info:');
for c1 in ( select * from sys.gv_$session_connect_info where sid = s.sid and inst_id='&inst_id') loop
dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
end loop;
dbms_output.put_line('Locks:');
for c1 in ( select /*+ RULE */ decode(l.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.request) ) lrequest,
decode(l.type, 'MR', o.name,
'TD', o.name,
'TM', o.name,
'RW', 'FILE#='||substr(l.id1,1,3)||
' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) objname
from sys.gv_$lock l, sys.obj$ o
where sid = s.sid
and inst_id='&inst_id'
and l.id1 = o.obj#(+) )
loop
dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
end loop;
dbms_output.put_line('=====================================================================');
END LOOP;
close cur_c1;
exception
when no_data_found then
dbms_output.put_line('Unable to find process id &&SID for the instance number '||'&inst_id'||' !!!');
dbms_output.put_line('=====================================================================');
return;
when others then
dbms_output.put_line(sqlerrm);
return;
END;
/
undef SID
set heading on
set verify on
set feedback on
05. To check holder and waiter session in database.
@holder_waiter_session
set lines 200
col sess for a25
select inst_id,decode(request,0,'Holder: ','Waiter: ')|| sid sess,id1,id2,lmode, request, type from gv$lock
where (id1,id2,type)in(select id1,id2,type from gv$lock where request>0)
order by id1,request;
06. TO check Blocking session in database .
@blocking_session_details
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;
07. To Check Object locking details
@lock_details
set lines 200 pages 8000
col OBJECT_NAME for a30
SELECT a.INST_ID,/*+rule*/ a.sid,a.serial#, a.username,c.os_user_name,a.terminal,
b.object_id,substr(b.object_name,1,40) object_name
from gv$session a, dba_objects b, gv$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id;
08. To check tablespace details with pct size wise.
REM TABLESPACE DETAILS
col TABLESPACE_NAME for a30
REM Tablespace & freespace
set linesize 150
set pages 10000
SELECT tablespace_name,ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB,
ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,
ROUND(SUM(max_mb)) MAX_SZ_MB,
ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL
FROM (
SELECT tablespace_name, SUM(bytes)/1024/1024/1024 FREE_MB,
0 TOTAL_MB, 0 MAX_MB
FROM dba_free_space
GROUP BY tablespace_name
UNION
SELECT tablespace_name, 0 CURRENT_MB,
SUM(bytes)/1024/1024/1024 TOTAL_MB,
SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024/1024 MAX_MB
FROM dba_data_files
GROUP BY tablespace_name)
GROUP BY tablespace_name
order by 5;
9. To check how much time take query will time to complete.
*sofar*
col target for a30
col opname for a30
select username,sid,opname,target,sofar,totalwork,(sofar*100)/totalwork from v$session_longops where sofar<totalwork;
10. To connect pl/sql developer software without tns name
Database -- scan_name:scan_port/service_name
Database -- servicename or ip:port_number//
11. To check Ports numbers
Note : This is based on environment ,below ports is just exmaples :
show parameter remote_listener
srvctl config scan --> to find scan port and scan IP
srvctl config nodeapps -a --> to find virtual port and IP
FOR SCAN IP :1651
FOR Physical IP and Virtual IP : 1621
12. To check port open or not
telnet IP port_number --> command
which port we have to give scan port or vip port -------for SCAN ip give 1651 ( Note :Based on environment )
for vip and pysical is 1621 ( Note :Based on environment )
srvctl config scan --> to check scan name and ip
srvctl config scan_listener --> to check scan port
srvctl config nodeapps -a ---> to check vip name and IP
srvctl config listener or show parameter remote_listener --> to check vip port
\
13. To deleting the trace files,trim files and audti files in backup ground
nohup find . -name '*.trc' -mtime +3 -exec rm -r {} \; &
nohup find . -name '*.trm' -mtime +3 -exec rm -r {} \; &
nohup find . -name '*.aud' -mtime +2 -exec rm -r {} \; &
For Super Cluster server recomemded by ACS :
Current user below command update 10Feb20 -->
nohup find . -name '*.trm' -type f -print0 -mtime +2 | xargs -0 -n1 rm &
nohup find . -name '*.trc' -type f -print0 -mtime +2 | xargs -0 -n1 rm &
14. kill at os level related to database related
Note : don't use without proper knowing the output
ps -ef|grep LOCAL=NO|grep -i INVEXP|awk '{print "kill -9 "$2}' > kill.sh
15. To Take export meta data backup
expdp directory=EXP_MCLR_23jan19 dumpfile=EXP_schema_metadata_date_%U.dmp logfile=EXP_schema_metadata_date.log schemas=Schemaname content=metadata_only compression=all cluster=n metrics=y
16. To get ddl information of the Objects(procedure,package,dblink,table,materialized view etc...)
for proper format setting ---
set long 999999999 longchunksize 20000 pagesize 0 linesize 1000 --> this will give proper output
column ddl format a1000
set heading on or off
To get ddl information of the procedure:
set long 999999999
select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME','SCHEMA_NAME') from dual;
To get ddl metarailed view :
set long 999999999
select dbms_metadata.get_ddl('MATERIALIZED_VIEW','MV_NAME','SCAHEMA_NAME') from dual;
To get ddl information of the package and package body :
set long 999999999
select dbms_metadata.get_ddl('PACKAGE','PACKAGE_NAME','SCAHEMA_NAME') from dual;
SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','PACKAGE_NAME','SCAHEMA_NAME') FROM DUAL;
To get ddl information of the TRIGGER :
set long 999999999
SELECT DBMS_METADATA.GET_DDL('TRIGGER','TRIGGER_NAME','SCAHEMA_NAME') FROM DUAL;
To get ddl information of the DB link:
set long 999999999
SELECT DBMS_METADATA.GET_DDL('DB_LINK','DBLINK_NAME','SCAHEMA_NAME') FROM DUAL;
To get ddl information of the dba scheduler job data:
set long 999999999
select dbms_metadata.get_ddl('PROCOBJ','SCHEDULER_NAME','SCAHEMA_NAME') from dual;
17. To run .sh script in nohup
nohup sh /export/home/oracle/gatherstatics//_schemastats.sh &
18. To run .sql file script in nohup
nohup sqlplus /nolog < script_file_name.sql > script_nohup.log &
19. To run .rcv file in nohup
nohup rman target / <rman_script_file_name.rcv> rman_script_nohup.log &
20. To check nohup commands related output will display every 50 seconds
while true;do jobs;sleep 50;done
21. To Display limited number of files
ls -lrt log_[1-60]*.xml
22. connect to rman ( From primary to standy )
rman target sys/oracle@DRSERVICENAME
23. To kill particular session in database level
Syntax : alter system kill session 'SID,SERIAL#,@INSTANCENUMBER' immediate;
Ex:
alter system kill session '324,7879,@2' immediate;
24. To find stale stats and last analyzed for particular schema
stale --> yes means we have to run stats gather,no means no need.
select
table_name,
stale_stats,
last_analyzed
from
dba_tab_statistics
where
owner ='schema_name'
order by
last_analyzed desc, table_name asc;
25. To display (Dynamic query) grants from user to user for tables
SQL> select 'grant '||' '||'select'||' '||'on '||' '||'FROMUSER'||'.'||table_name ||' '||'to TOUSER;' from dba_tables where owner='FROMUSER';
26. To Copy command thread in asm mount point in grid user,inside the asmcmd
cp --port 1621 +DATA_R/CBSNGEOY/BACKUPSET/2016_10_14/nnndf0_TAG20161014T182103_0.962.925237267 sys/oracle@serverip.+ASM1:+DATA_P/BACKUP/2016_10_14/nnndf0_TAG20161014T182103_0
--for DBNAME--------
cp --port 1621 +DATA_DG/DBNAME/BACKUPSET/2016_10_20/annnf0_TAG20161020T163553_0.442.925749361 sys/oracle@serverip.+ASM1:+DATA_P/STAGE_RMAN/annnf0_TAG20161020T163553_0
27. To check unix mount point it will shows top consumed in top.
$du -sm *|sort -nr
28. To Set time in database level
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
29. To kill inactive,active with particular module session
select 'alter system kill session ''' || sid || ',' || serial# ||',@'||inst_id||''' immediate;' from gv$session where status='INACTIVE' and username='CARDS';
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from gv$session where status='INACTIVE' and username in ('');
select 'alter system kill session ''' || sid || ',' || serial# ||',@'||inst_id||''' immediate;' from gv$session where status='INACTIVE' and Module='PL/SQL Developer';
select 'alter system kill session ''' || sid || ',' || serial# ||',@'||inst_id||''' immediate;' from gv$session where status='INACTIVE' and Osuser='Logi Info .Net v4.0' and Module='w3wp.exe';
30. To kill particular with sql_id both nodes
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from gv$session where SQL_ID='274ksuz6zf2xr';
select 'alter system kill session ''' || sid || ',' || serial# ||',@'||inst_id||''' immediate;' from gv$session where sql_id='274ksuz6zf2xr';
31. To kill particular session related with db and os level ( example : select list query table)
SELECT distinct S.SID, S.SERIAL#, Q.SQL_TEXT
FROM V$SESSION S, V$SQL Qf
WHERE S.USERNAME IS NOT NULL
AND S.STATUS = 'ACTIVE'
AND S.SQL_ID IS NOT NULL
AND Q.SQL_ID = S.SQL_ID
AND Q.SQL_TEXT like ('SELECT%listquerytable%')
and username <> 'SYS';
select distinct '!kill -9 '||p.spid
||chr(10)||
'alter system kill session '''||s.sid||','||s.serial#||''' immediate;'
from v$session s, v$process p, V$SQL Q
where s.username <> 'SYS' and s.username is not null and s.SQL_ID IS NOT NULL
AND Q.SQL_ID = s.SQL_ID
AND Q.SQL_TEXT like ('SELECT%listquerytable%')
and s.paddr=p.addr
/
32. To view first ten files
ls -al|head 11
33. ASM disk space check
set lines 150 pages 200
select name,type,total_mb/1024,free_mb/1024,USABLE_FILE_MB/1024 from v$asm_diskgroup;
34. To check Tablescape space in database level.
col TABLESPACE_NAME for a30
REM Tablespace & freespace
set linesize 150
set pages 10000
SELECT tablespace_name,ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB,
ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,
ROUND(SUM(max_mb)) MAX_SZ_MB,
ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL
FROM (
SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,
0 TOTAL_MB, 0 MAX_MB
FROM dba_free_space
GROUP BY tablespace_name
UNION
SELECT tablespace_name, 0 CURRENT_MB,
SUM(bytes)/1024/1024 TOTAL_MB,
SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
FROM dba_data_files
GROUP BY tablespace_name)
GROUP BY tablespace_name
order by 5;
35. To check TEMP tablespace size and monitor purpose
TEMP Monitoring :
select name,db_unique_name,TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024/1024 as tbs_size,FREE_SPACE/1024/1024/1024 as tbs_free,
round(((TABLESPACE_SIZE/1024/1024/1024)-(FREE_SPACE/1024/1024/1024))/(TABLESPACE_SIZE/1024/1024/1024),2)*100 as pct_full
from dba_temp_free_space a,v$instance,v$database;
TEMP SPACE--------------------
select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024/1024,BLOCKS,STATUS,MAXBYTES/1024/1024/1024,MAXBLOCKS/1024/1024/1024 from dba_temp_files;
TEMP FREE SPACE ----------------------
select TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024/1024,ALLOCATED_SPACE/1024/1024/1024,FREE_SPACE/1024/1024/1024 from dba_temp_free_space;
TABLESPACE FREE SPACE
select FILE_NAME,BYTES/1024/1024/1024,STATUS from dba_data_files where tablespace_name='
select b.Total_MB,b.Total_MB - round(a.used_blocks*8/1024) Current_Free_MB,round(used_blocks*8/1024) Current_Used_MB,round(max_used_blocks*8/1024) Max_used_MB from v$sort_segment a,(select round(sum(bytes)/1024/1024) Total_MB from dba_temp_files ) b;
36. To check default temp tablespace
SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE'
37. To check top 20 consumed tablespace detail
SELECT grantee, privilege FROM dba_sys_privs where privilege like '%UNLIMITED%TABLE%' and grantee in (select username from dba_users where account_status='OPEN'and username not in ('SYS','SYSTEM','SYSMAN','GGS','DBSNMP','DBMON','DBSPI','OUTLN'));
38. To check datafile for particular tablespace releated
set line 160
set pagesize 500
col name for a70
col TABLESPACE_NAME for a25
select df.NAME,df.TABLESPACE_NAME,round(df.BYTES/1024/1024) Allocated,
round(nvl(dfs.BYTES/1024/1024,0)) FREE_SPACE, round(((df.BYTES/1024/1024)-nvl((dfs.BYTES/1024/1024),0))) USED_SPACE
from v$datafile_header df,
(select file_id,tablespace_name,sum(bytes) bytes
from dba_free_space
group by file_id,tablespace_name) dfs
where df.FILE#=dfs.FILE_ID (+)
AND df.TABLESPACE_NAME like upper('&Tablespace')
order by CREATION_TIME,free_space;
39. To Rebuild index using below command.
alter index schemaname.index_name rebuild online;
40. Rebuild index parallel (online) and no parallel (offline)
alter index CXPSADM.SYS_C0097820 rebuild parallel 8;
alter index CXPSADM.SYS_C0097820 rebuild online noparallel;
41. To compile invalid objects
Ex:
alter materialized view schemaname.MV_Name compile;
42. To Check database size
Physical size :
select sum(bytes/1024/1024/1024) Physical_size_gb from dba_data_files;
Actual size(logical size) :
select sum(bytes/1024/1024/1024) Actual_size_gb from dba_segments;
43. To Check RMAN Running backup in database
rman bkp status
Set line 2000
col end_time for a20
col start_time for a20
col time_taken_display for a20
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
time_taken_display,
input_bytes/1024/1024/1024,
output_bytes/1024/1024/1024
from V$RMAN_BACKUP_JOB_DETAILS
order by start_time;
44. To check the list of Privileges granted to a User (Grantee) :
Note : just for understanding :grantee --- means privilege taken and grantor privilege given
col Grantor for a12
col Grantee for a12
col Table_name for a12
set line 120
select GRANTEE, OWNER "OBJECT_OWNER", GRANTOR, TABLE_NAME, PRIVILEGE from DBA_TAB_PRIVS where GRANTEE='Schema_name';
45. To check the list of Roles granted to a User (Grantee) :
col Granted_role for a12
col Grantee for a12
set line 120
select GRANTEE, GRANTED_ROLE, ADMIN_OPTION from DBA_ROLE_PRIVS where GRANTEE=' ';
46: To check the System Privileges granted to a particular Role:
col Role for a12
set line 80
select ROLE, PRIVILEGE, ADMIN_OPTION from ROLE_SYS_PRIVS where ROLE=' ';
47. To check the Object Privileges granted to a particular Role:
col Role for a12
col Owner for a12
col Table_name for a12
col Column_name for a12
set line 120
select ROLE, OWNER "OBJECT_OWNER", TABLE_NAME, COLUMN_NAME, PRIVILEGE, GRANTABLE from ROLE_TAB_PRIVS where ROLE=' ';
48. To create DB link (DATABASE LINK)
conn Schema_name/Schema_name_pwd
create database link <dblink_name> connect to user identified by <user password> using '<database name>';
conn Schema_name/Schema_name#123
create database link DB_LINK_NAME connect to SCHEMA_NAME identified by pwd using 'service_name';
49. To check index ,related to particular schema and particular table.
select owner,segment_type,segment_name,sum(bytes)/1024/1024/1024 from dba_segments where segment_name in (select index_name from dba_indexes where table_name='FT_CORE' and table_owner='CXPSADM') group by owner,segment_type,segment_name
order by owner,segment_type,segment_name;
50. To Check How much RMAN RESTORE STATUS.
set lines 1500
set pages 100
col CLI_INFO format a10
col spid format a5
col ch format a20
col seconds format 999999.99
col filename format a65
col bfc format 9
col "% Complete" format 999.99
col event format a40
set numwidth 10
select s.inst_id, o.sid, CLIENT_INFO ch, context, sofar, totalwork,
round(sofar/totalwork*100,2) "% Complete"
FROM gv$session_longops o, gv$session s
WHERE opname LIKE 'RMAN%'
AND opname NOT LIKE '%aggregate%'
AND o.sid=s.sid
AND totalwork != 0
AND sofar <> totalwork;
Restore Status :
select inst_id, sid, CLIENT_INFO ch, seq#, event, state, wait_time_micro/1000000 seconds
from gv$session where program like '%rman%' and
wait_time = 0 and
not action is null;
51. To Creating new user/schema
CREATE USER Schema_name IDENTIFIED BY passpwd DEFAULT TABLESPACE tablespace_name TEMPORARY TABLESPACE TEMP;
GRANT connect TO Schema_name ;
GRANT RESOURCE TO Schema_name ;
52. To check owner of object
select object_name,object_type,owner from dba_objects where object_name=' ';
53. To check how much query remaining to execute in database level.
@sofar
col target for a30
col opname for a30
select username,sid,opname,target,sofar,totalwork,(sofar*100)/totalwork from v$session_longops where sofar<totalwork;
54. Fragmentation check query
set lines 9999
set pages 2000
select owner,table_name,
round(((blocks*8)/1024),2) "table size mb",
round((num_rows*avg_row_len/1024/1024),2) "actual data in table mb",
round(((blocks*8)/1024),2)- round((num_rows*avg_row_len/1024/1024),2) "wasted space mb",
((round(((blocks*8)/1024),2)- round((num_rows*avg_row_len/1024/1024),2)) / (round(((blocks*8)/1024),2)) ) *100 "percent fragmented"
from dba_tables
where blocks != 0
and owner = '&OWNER'
order by 1,4 desc
/
55. To check OEM Agent status (Oracle Entireprice Manager)
1-- ps -ef | grep oem
2--cd /u01/app/oracle/product/oem13c/agent13c/
3--cd agent_inst
4--cd bin
pwd----/u01/app/oracle/product/oem13c/agent13c/agent_inst/bin
run---- ./emctl status agent
./emctl pingOMS
56. To check running,holder session in database
running active sessions check:
ses.sql
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;
check holder waiter session :
hw.sql
set lines 200
col sess for a25
select inst_id,decode(request,0,'Holder: ','Waiter: ')|| sid sess,id1,id2,lmode, request, type from gv$lock
where (id1,id2,type)in(select id1,id2,type from gv$lock where request>0)
order by id1,request;
57. Error solution suggeted oraclesupport
[cssd(8020)]CRS-1603:CSSD error
through grid
crsctl stat res -t
ps -ef | grep d.bin
crsctl stat res -t -init
58. To check Running RMAN backup in database.
set linesize 150
set pagesize 300
col start_time for a20
col end_time for a20
col time_taken_display for a9
col output_bytes_display for a12
select input_type,
command_id,
status,
to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,
-- to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,
output_bytes_display,
time_taken_display
from v$rman_backup_job_details
--Where status = 'RUNNING'
--where COMMAND_ID like 'FULL%'
--where INPUT_TYPE like'DB%'
order by session_key desc;
58.1 rman backup check:
Set line 2000
col end_time for a20
col start_time for a20
col time_taken_display for a20
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yyyy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yyyy hh24:mi') end_time,time_taken_display,
input_bytes/1024/1024/1024,
output_bytes/1024/1024/1024
from V$RMAN_BACKUP_JOB_DETAILS
order by start_time;
59. To check backup is running on tape or device
set lines 200
set pages 2000
col TIME_TAKEN_DISPLAY for a10
col start_time for a20
col end_time for a20
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
col HOST_NAME for a20
select distinct i.instance_name,i.host_name,r.SESSION_KEY,b.DEVICE_TYPE, r.STATUS,r.START_TIME,r.END_TIME,r.time_taken_display,r.OUTPUT_BYTES/1024/1024 "SIZE_MB",
r.OUTPUT_BYTES/1024/1024/1024 "SIZE_GB",b.INCREMENTAL_LEVEL "INCR_LEV" from V$RMAN_BACKUP_JOB_DETAILS r,V$BACKUP_SET_DETAILS b,v$instance i where /*r.START_TIME >= SYSDATE-7 and */
r.SESSION_KEY=b.SESSION_KEY and b.INCREMENTAL_LEVEL is not null order by r.SESSION_KEY;
60. To check Running backup is incremental level 0 or 1 in tape
set lines 200
set pages 2000
col TIME_TAKEN_DISPLAY for a10
col start_time for a20
col end_time for a20
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
col HOST_NAME for a10
select distinct i.instance_name,r.SESSION_KEY,b.DEVICE_TYPE, r.STATUS,r.START_TIME,r.END_TIME,r.time_taken_display,r.OUTPUT_BYTES/1024/1024 "SIZE_MB",
r.OUTPUT_BYTES/1024/1024/1024 "SIZE_GB",b.INCREMENTAL_LEVEL "INCR_LEV" from V$RMAN_BACKUP_JOB_DETAILS r,V$BACKUP_SET_DETAILS b,v$instance i where --r.START_TIME >= SYSDATE-7 and
r.SESSION_KEY=b.SESSION_KEY and b.INCREMENTAL_LEVEL is not null order by r.SESSION_KEY;
61. To check audit table backup aud$
select count(*) from sys.aud$;
col owner format a20
col segment_name format a20
select sum(bytes/1024/1024),segment_name,owner from dba_segments where segment_name='AUD$' group by segment_name,owner;
62. To check locking table details in database
select object_name,s.sid,s.serial#,p.spid from v$locked_object i,dba_objects o,gv$session s,gv$process p where i.object_id=o.object_id and i.session_id=s.sid and s.paddr=p.addr;
63. To check locking object in particular table
set lines 200 pages 8000
col OBJECT_NAME for a30
SELECT a.INST_ID,/*+rule*/ a.sid,a.serial#, a.username,c.os_user_name,a.terminal,
b.object_id,substr(b.object_name,1,40) object_name
from gv$session a, dba_objects b, gv$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id;
64. How to run schema stats gather
spool schema_stats.log
set time on timing on echo on feedback on
EXEC DBMS_STATS.gather_schema_stats('schemaname_1',degree=>4,cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC DBMS_STATS.gather_schema_stats('schemaname_2',degree=>4,cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC DBMS_STATS.gather_schema_stats('schemaname_3',degree=>4,cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
spool off;
exit;
65. To Run schema stats gather in .sh file
cat /u02/slm/gatherstats/DBNAME_schemastats.sh
export ORACLE_HOME=/ora_home/app/product/12.1.0/dbhome_1
export ORACLE_SID=DBNAME2
export PATH=$PATH:$ORACLE_HOME/bin
TODAY=`date '+%d%m%Y_%H%M'`
$ORACLE_HOME/bin/sqlplus "/as sysdba" @/u02/slm/gatherstats/DBNAME_schemastats.sql
cp /u02/slm/gatherstats/log/DBNAME_schemastats.log /u02/slm/gatherstats/log/DBNAME_schemastats_"${TODAY}".log
exit;
cat /u02/slm/gatherstats/DBNAME_schemastats.sql
set time on
set timing on
set echo on
set feedback on
set lines 200
set pages 2000
spool /u02/slm/gatherstats/log/DBNAME_schemastats.log
exec DBMS_STATS.GATHER_schema_stats(OWNNAME => 'SCHEMANAME',degree=> 6, CASCADE=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
spool off;
exit;
To run:
nohup sh /u02/slm/gatherstats/DBNAME_schemastats.sh &
66. To run Table stats gather
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCHEMANAME',TABNAME =>'TABLE_NAME',degree=> 8, CASCADE=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCHEMANAME',TABNAME =>'TABLE_NAME',degree=> 8, CASCADE=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
67. To unlock the object while doing stats gather
exec dbms_stats.unlock_table_stats(OWNNAME => 'SCHEMA_NAME',TABNAME =>'TABLE_NAME');
exec dbms_stats.lock_table_stats(OWNNAME => 'SCHEMA_NAME',TABNAME =>'TABLE_NAME');
68. To Run dictionary&object stats of the database.
set echo off
set heading on
set feedback off
set termout off
set lines 400 pages 1000
conn / as sysdba
spool gather_stats_dic_object.log
exec dbms_stats.gather_fixed_objects_stats();
EXEC DBMS_STATS.gather_dictionary_stats;
spool off;
exit
69. To run .sql files in nohup mode in two ways givn in below.
1. connect without as sysdba :
nohup sqlplus /nolog < fixed_and_dict_stats.sql > fixed_and_dict_stats_nohup.log &
2. connect with as sysdba :
nohup sqlplus / as sysdba @scprod_procedure_16nov19.sql > scprod_procedure_16nov19_nohup_16nov19.log &
70. To divert the online services from one node to another node using below command.
srvctl relocate service -d HDPPRD -s HDPDB<service_name> -i HDPPRD1<current service instance_name> -t HDPPRD2<target instance_name>
71. To check gg log (golden gate)
gg check log
view report replicate_name
72. To set the defer and enable the log archive dest
Defer :
alter system set LOG_ARCHIVE_DEST_state_2='DEFER' sid='*';
Enable:
alter system set LOG_ARCHIVE_DEST_state_2='ENABLE' sid='*';
73. To check how much resotre is completed.
select SID,MBYTES_PROCESSED/1024 "MBYTES_PROCESSED",STATUS,OPERATION,INPUT_BYTES/1024/1024/1024 "INPUT_BYTES GB",OUTPUT_BYTES/1024/1024/1024 "OUTPUT_BYTES GB" from V$RMAN_STATUS where status='RUNNING';
74. To check scheduler job in db
col LAST_START_DATE format a30
col JOB_NAME format a15
col NEXT_RUN_DATE format a35
col LAST_RUN_DURATION format a35
select JOB_NAME,NEXT_RUN_DATE,ENABLED,LAST_START_DATE,LAST_RUN_DURATION,FAILURE_COUNT from dba_scheduler_jobs where job_name in ('JOB_NAME')
select
log_date,
job_name,
status,
run_duration,
ERROR#
from
dba_scheduler_job_run_details
where job_name in ('JOB_NAME');
set lines 200 pages 200
col job_name format a30
col stauts format a10
col log_date format a45
col RUN_DURATION format a15
select
log_date,
job_name,
status,
run_duration,
ERROR#
from
dba_scheduler_job_run_details
where job_name in ('JOB_NAME')
order by log_date asc;
75. To check Particular running Job Details
col owner format a10
col job_name format a30
col START_DATE format a35
set lines 200 pages 200
select owner,JOB_NAME,START_DATE,NEXT_RUN_DATE from all_scheduler_jobs where job_name in ('JOB_NAME'');
76. To check error related to job_scheduler
select
log_id,
to_char(log_date,'dd/mm/yyyy hh24:mi:ss') log_date,
substr(job_name, 1,20) job_name,
substr(status, 1,10) status,
additional_info operation
from
dba_scheduler_job_log
where
job_name='JOB_NAME'
order by
log_date ;
77. To check disks using kfod command
kfod disks=all dscvgroup=true status=true
78. To Check PSU patch in db(at os level)
oracle@hostname:…product/12.1.0/dbhome_1/OPatch$ ./opatch lsinventory| grep -i desc
ARU platform description:: Solaris Operating System (SPARC 64-bit)
Patch description: "OCW PATCH SET UPDATE 12.1.0.2.181016 (28259914)"
Patch description: "Database Bundle Patch : 12.1.0.2.181016 (28259867)"
Patch description: "Database PSU 12.1.0.2.170117, Oracle JavaVM Component (JAN2017)"
oracle@hostname:…product/12.1.0/dbhome_1/OPatch$ ./opatch lsinventory| grep -i applied
Patch 21065289 : applied on Thu Nov 22 18:00:39 IST 2018
Patch 20898351 : applied on Thu Nov 22 17:58:02 IST 2018
Patch 28259914 : applied on Thu Nov 22 16:43:36 IST 2018
Patch 28259867 : applied on Thu Nov 22 16:41:34 IST 2018
Patch 19339555 : applied on Tue Apr 11 15:54:29 IST 2017
Patch 24917972 : applied on Tue Mar 21 14:43:35 IST 2017
79. To Check how many archive's are generated hour base details
SELECT instance ins,
log_date "DATE" ,
lpad(to_char(NVL( COUNT( * ) , 0 )),6,' ') Total,
lpad(to_char(NVL( SUM( decode( log_hour , '00' , 1 ) ) , 0 )),3,' ') h00 ,
lpad(to_char(NVL( SUM( decode( log_hour , '01' , 1 ) ) , 0 )),3,' ') h01 ,
lpad(to_char(NVL( SUM( decode( log_hour , '02' , 1 ) ) , 0 )),3,' ') h02 ,
lpad(to_char(NVL( SUM( decode( log_hour , '03' , 1 ) ) , 0 )),3,' ') h03 ,
lpad(to_char(NVL( SUM( decode( log_hour , '04' , 1 ) ) , 0 )),3,' ') h04 ,
lpad(to_char(NVL( SUM( decode( log_hour , '05' , 1 ) ) , 0 )),3,' ') h05 ,
lpad(to_char(NVL( SUM( decode( log_hour , '06' , 1 ) ) , 0 )),3,' ') h06 ,
lpad(to_char(NVL( SUM( decode( log_hour , '07' , 1 ) ) , 0 )),3,' ') h07 ,
lpad(to_char(NVL( SUM( decode( log_hour , '08' , 1 ) ) , 0 )),3,' ') h08 ,
lpad(to_char(NVL( SUM( decode( log_hour , '09' , 1 ) ) , 0 )),3,' ') h09 ,
lpad(to_char(NVL( SUM( decode( log_hour , '10' , 1 ) ) , 0 )),3,' ') h10 ,
lpad(to_char(NVL( SUM( decode( log_hour , '11' , 1 ) ) , 0 )),3,' ') h11 ,
lpad(to_char(NVL( SUM( decode( log_hour , '12' , 1 ) ) , 0 )),3,' ') h12 ,
lpad(to_char(NVL( SUM( decode( log_hour , '13' , 1 ) ) , 0 )),3,' ') h13 ,
lpad(to_char(NVL( SUM( decode( log_hour , '14' , 1 ) ) , 0 )),3,' ') h14 ,
lpad(to_char(NVL( SUM( decode( log_hour , '15' , 1 ) ) , 0 )),3,' ') h15 ,
lpad(to_char(NVL( SUM( decode( log_hour , '16' , 1 ) ) , 0 )),3,' ') h16 ,
lpad(to_char(NVL( SUM( decode( log_hour , '17' , 1 ) ) , 0 )),3,' ') h17 ,
lpad(to_char(NVL( SUM( decode( log_hour , '18' , 1 ) ) , 0 )),3,' ') h18 ,
lpad(to_char(NVL( SUM( decode( log_hour , '19' , 1 ) ) , 0 )),3,' ') h19 ,
lpad(to_char(NVL( SUM( decode( log_hour , '20' , 1 ) ) , 0 )),3,' ') h20 ,
lpad(to_char(NVL( SUM( decode( log_hour , '21' , 1 ) ) , 0 )),3,' ') h21 ,
lpad(to_char(NVL( SUM( decode( log_hour , '22' , 1 ) ) , 0 )),3,' ') h22 ,
lpad(to_char(NVL( SUM( decode( log_hour , '23' , 1 ) ) , 0 )),3,' ') h23
FROM (
SELECT thread# INSTANCE ,
TO_CHAR( first_time , 'DD-MON-YY' ) log_date ,
TO_CHAR( first_time , 'hh24' ) log_hour
FROM v$log_history
)
GROUP BY INSTANCE ,
log_date
ORDER BY INSTANCE ,
to_date(log_date,'DD-MON-YY');
80. Dynamic Resize tablespace query (it will shows which tablespace we can resize )
set lines 168 pages 5000
SELECT
'/* '||to_char(CEIL((f.blocks-e.hwm)*(f.bytes/f.blocks)/1024/1024),99999999)||' Freeable MB */ ' ||'alter database datafile '''||file_name||''' resize '||CEIL(e.hwm*(f.bytes/f.blocks)/1024/1024)||'M;' DF_RESIZE FROM DBA_DATA_FILES f,
SYS.TS$ t,
(SELECT ktfbuefno relative_fno,ktfbuesegtsn ts#,
MAX(ktfbuebno+ktfbueblks) hwm FROM sys.x$ktfbue GROUP BY ktfbuefno,ktfbuesegtsn) e
WHERE
f.relative_fno=e.relative_fno and t.name=f.tablespace_name and t.ts#=e.ts#
and f.blocks-e.hwm > 1000
ORDER BY f.blocks-e.hwm DESC;
81. As per audit based on environment below lister should be on.
ADMIN_RESTRICTIONS_LISTENER = ON
ADMIN_RESTRICTIONS_LISTENER_SCAN1 = ON
ADMIN_RESTRICTIONS_LISTENER_SCAN2 = ON
ADMIN_RESTRICTIONS_LISTENER_SCAN3 = ON
82. To Check failed Login details ( wrong password hits while connecting to schema)
select USERID, userhost, decode(returncode,01017,'Login Error','Acount Locked') "ISSUE", spare1,
TO_CHAR ( CAST(
( FROM_TZ(
CAST(
TO_DATE(
TO_CHAR( ntimestamp# , 'DD/MM/YYYY HH:MI PM'),
'DD/MM/YYYY HH:MI PM'
)
AS TIMESTAMP
) ,
'GMT'
) AT LOCAL
)
AS TIMESTAMP)
, 'DD/MM/YYYY HH:MI PM') "Time",
sqltext,
comment$text from SYS.aud$
where ( returncode=1017 OR returncode=28000 )
and userid in 'SCHEMA_NAME'
order by ntimestamp# desc ;
83. Dynamic query to give all select privilege to all tables (from user to user)
spool /u02/slm/grants_<date>.sql
select 'grant '||' '||'select'||' '||'on '||' '||'FROM_USERNAME'||'.'||table_name ||' '||'to TO_USERNAME;'
from dba_tables where owner='FROM_USERNAME';
spool off
84. To Give only table,view grants from user to user
select 'grant select on '||owner||'.'||object_name||' to TO_USERNAME;' from dba_objects where owner='FROM_USERNAME' and object_type in ('TABLE','VIEW');
85. To give sys level privileges to User or grant resorce.
grant CREATE TRIGGER to <USERNAME>;
grant CREATE SEQUENCE to <USERNAME>;
grant CREATE TYPE to <USERNAME>;
grant CREATE PROCEDURE to <USERNAME>;
grant CREATE CLUSTER to <USERNAME>;
grant CREATE OPERATOR to <USERNAME>;
grant CREATE INDEXTYPE to <USERNAME>;
grant CREATE TABLE to <USERNAME>;
grant create session to <USERNAME>;
or
grant resource to Username;
86. To create logfile using rman (while taking rman prompt it will record like spool use of below command)
rman target / log=logfilename.txt
87. To Flush Shared Pool with particluar SQL ID:
Note : Don't use ,if we dont knw exact use of this command in production
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '9yc%';
ADDRESS HASH_VALUE
---------------- ----------
000000095FD77CF0 908321886
SQL> exec DBMS_SHARED_POOL.Schame_name1 ('000000095FD77CF0, 908321886', 'C');
88. As per oracle docs difference between dirrentail and cumulative backup.
A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0
A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0
89. To check how much restoration status
set lines 168 pages 5000
select SID,MBYTES_PROCESSED/1024 "MBYTES_PROCESSED",STATUS,OPERATION,INPUT_BYTES/1024/1024/1024 "INPUT_BYTES GB",OUTPUT_BYTES/1024/1024/1024 "OUTPUT_BYTES GB" from V$RMAN_STATUS where status='RUNNING';
90. To enable the audit( after using below command,we got updated details in locked account details.
SQL> audit session whenever not successful ;
Audit succeeded.
91. To revoke and grant the user
revoke select, update, insert on emp from slm;
revoke select, update, insert on emp from slm;
grant select, update, insert on emp to slm;
9. Export backup ,dump file storing different location in mount points.
[oracle@Hostname]$ cat /orabkp_slm/EXPBKP/script/expdp_DBNAME_full_cron.sh
[oracle@Hostname]$ cat /orabkp_slm/EXPBKP/script/expdp_DBNAME_full.sh
#!/bin/sh
export ORACLE_BASE=/oracle/app/product
export ORACLE_HOME=/oracle/app/product/11.2.0.4/db_home1
export ORACLE_SID=DBNAME
export DT=`date '+%d%m%Y_%H%M%S'`
export EXPFILE=expdp_DBNAME_`date '+%d%m%Y_%H%M%S'`
/oracle/app/product/11.2.0.4/db_home1/bin/expdp parfile=/orabkp_slm/EXPBKP/script/details.par dumpfile=EXP_BKP:${EXPFILE}_DIR1_%U.dmp,EXP_BKP1:${EXPFILE}_DIR2_%U.dmp,EXP_BKP2:${EXPFILE}_DIR3_%U.dmp,EXP_BKP3:${EXPFILE}_DIR4_%U.dmp directory=EXP_BKP logfile=expdp_DBNAME_$DT.log FULL=Y compression=all parallel=16
/usr/bin/find /orabkp_slm/EXPBKP/ -name 'expdp_DBNAME_*.dmp*' -mtime +1 -exec /bin/rm {} \;
/usr/bin/find /orabkp_slm/EXPBKP1/ -name 'expdp_DBNAME_*.dmp*' -mtime +1 -exec /bin/rm {} \;
/usr/bin/find /orabkp_slm/EXPBKP2/ -name 'expdp_DBNAME_*.dmp*' -mtime +1 -exec /bin/rm {} \;
/usr/bin/find /orabkp_slm/EXPBKP3/ -name 'expdp_DBNAME_*.dmp*' -mtime +1 -exec /bin/rm {} \;
/usr/bin/find /orabkp_slm/EXPBKP/ -name 'expdp_DBNAME_*.log' -mtime +15 -exec /bin/rm {} \;
##cd /orabkp_slm/EXPBKP/
##gzip expdp_DBNAME_*.dmp
93. To check crs log file location
example :
/asm_home/app/grid_base/diag/crs/Hostname/crs/trace
94. desc sys.user$ details
NAME – name for user or role
TYPE# – 0 for role or 1 for user
CTIME – the date of creation
PTIME – the date the password was last changed
EXPTIME – the date the password has last expired
LTIME – the date the resource was last locked
LCOUNT – number of failed logon
95. To check object is locked or not
SELECT B.Owner, B.Object_Name, A.Oracle_Usernam e, A.OS_User_Name
FROM V$Locked_Object A, All_Objects B
WHERE A.Object_ID = B.Object_ID;
96. To check whic object user is accessing
select * from gv$access where object='';
97. wallet not open ( this is related to environment issue,)while converting snapshot to physical ,
Note :simply set the database environment and check
take directly down.
Before taking up fire below command :
srvctl setenv database -d DBNAME -T ORACLE_UNQNAME=DBNAME
Take Db up and check status
On sql prompt :
set lines 168 pages 5000
col WRL_PARAMETER for a40
select * from gv$encryption_wallet order by 1;
98. explan plan for query
syntax : explain plan for "QUERYL_DETAILS";
select * from table (dbms_xplan.display);
99. Don't Ask any comment, not display any comment in query
SET DEFINE OFF
100. To check how many object present in schema
col owner format a30
select count(1),owner,status,object_type from dba_objects where owner='SCHEMA_NAME' GROUP BY owner,status,object_type;
101. To check expdp status (attach file in expdp)
Example :
[oracle@Hostname EXP_SCHEMA_NAME_schema 02:08:48]$ expdp attach=SYS_EXPORT_SCHEMA_02
Export: Release 12.1.0.2.0 - Production on Mon Oct 7 14:08:54 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Job: SYS_EXPORT_SCHEMA_02
Owner: SYS
Operation: EXPORT
Creator Privs: TRUE
GUID: 944D134E49C9751DE0532C99510AEC28
Start Time: Monday, 07 October, 2019 11:52:29
Mode: SCHEMA
Instance: SLM
Max Parallelism: 8
Timezone: +00:00
Timezone version: 18
Endianness: LITTLE
NLS character set: AL32UTF8
NLS NCHAR character set: AL16UTF16
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND /******** AS SYSDBA directory=expdp dumpfile=Exp_SLM_SCHEMA_NAME_schema_Date_%U.dmp logfile=Exp_SLM_SCHEMA_NAME_schema_Date.log schemas=SCHEMA_NAME compression=all parallel=8 cluster=n
COMPRESSION ALL
State: EXECUTING
Bytes Processed: 31,675,147,640
Percent Done: 84
Current Parallelism: 8
Job Error Count: 0
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_%u.dmp
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_01.dmp
bytes written: 4,096
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_02.dmp
bytes written: 5,163,536,384
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_03.dmp
bytes written: 5,696,098,304
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_04.dmp
bytes written: 5,348,511,744
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_05.dmp
bytes written: 4,792,729,600
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_06.dmp
bytes written: 5,654,609,920
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_07.dmp
bytes written: 5,028,229,120
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_08.dmp
bytes written: 37,781,504
Worker 1 Status:
Instance ID: 1
Instance name: SLM
Host name: Hostname
Process Name: DW00
State: WORK WAITING
102. TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]
The possible values have the following effects:
SKIP leaves the table as is and moves on to the next object. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.
APPEND loads rows from the source and leaves existing rows unchanged.
TRUNCATE deletes existing rows and then loads rows from the source.
REPLACE drops the existing table and then creates and loads it from the source. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.
103. In full expdp backup,exlude two schemas
expdp system/<password> directory=DATA_PUMP_DIR dumpfile=EXPDAT%U.DMP FULL=y exclude=schema:\"IN \(\'SCOTT\',\'SYSTEM\')\"
[oracle@Hostname]$ cat /orabkp_slm/EXPBKP/script/details.par_bkp_01102019
userid=' / as sysdba'
EXCLUDE=SCHEMA:"IN ('Schame_name1','Schame_name2')"
parfile=/orabkp_slm/EXPBKP/script/details.par_bkp_01102019 dumpfile=EXP_BKP:EXPDP_DIR1_%U.dmp,EXP_BKP1:EXPDP_DIR2_%U.dmp,EXP_BKP2:EXPDP_DIR3_%U.dmp,EXP_BKP3:EXPDP_DIR4_%U.dmp directory=EXP_BKP logfile=expdp_DBNAME_.log FULL=Y compression=all parallel=16
104. server to server scp command
nohup scp * oracle@10.0.0.143:/slm1/BKP_DETAILS/ &
105. To check db link details
col owner format a15
col db_link format a20
col username format a15
col host format a35
set lines 200 pages 200
select * from dba_db_links where owner in ('schemaname');
106. To find fragmentation of the tables
list of the tables to find fragmented tables :
select owner,table_name,
round(((blocks*8)/1024),2) "table size mb",
round((num_rows*avg_row_len/1024/1024),2) "actual data in table mb",
round(((blocks*8)/1024),2)- round((num_rows*avg_row_len/1024/1024),2) "wasted space mb",
((round(((blocks*8)/1024),2)- round((num_rows*avg_row_len/1024/1024),2)) / (round(((blocks*8)/1024),2)) ) *100 "percent fragmented"
from dba_tables
where blocks != 0
and owner in ('');
top listed tables and tablespace details :
select t.owner,t.table_name, s.tablespace_name,round(s.bytes/1024/1024) actual_MB,round(((t.num_rows+1) * t.avg_row_len)/1024/1024) optimal_MB,round(s.bytes/1024/1024) -round(((t.num_rows+1) * t.avg_row_len)/1024/1024) CLAIMABLE_MB from dba_tables t, dba_segments s where t.owner = s.owner and t.table_name = s.segment_name and round(s.bytes/1024/1024) - round(((t.num_rows+1) * t.avg_row_len)/1024/1024) > 50 order by CLAIMABLE_MB desc;
107. Details autentication service
#none --> db able to connect
#nts --> application able to connect
108. bin directory actual permssion
Executable Directory
# chmod 750 $ORACLE _HOME/bin ## -rwxr-x---
109. TO check modification of the table count like insert,update,delete etc
SELECT TABLE_OWNER,
TABLE_NAME,
INSERTS,
UPDATES,
DELETES,
TIMESTAMP AS `LAST_CHANGE`
FROM DBA_TAB_MODIFICATIONS
WHERE TO_CHAR(TIMESTAMP,'DD/MM/YYYY') = TO_CHAR(sysdate,'DD/MM/YYYY')
ORDER BY LAST_CHANGE DESC;`
110. Normal DB Health check
select name,open_mode,database_role from v$database;
alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
select startup_time from v$instance;
select distinct(checkpoint_time) from v$datafile_header;
select thread#,max(sequence#) from v$archived_log a,v$database b where a.resetlogs_change#=b.resetlogs_change# group by thread# order by thread#;
select thread#,max(sequence#) from v$log_history a,v$database b where a.resetlogs_change#=b.resetlogs_change# group by thread# order by thread#;
Check tablespace,asm level and mount point size.
111. How to take level 0 Backup
oracle@Hostname:/rmanbkp/SLM_BKP$ cat rman_L0_jan.rcv
CONFIGURE DEVICE TYPE DISK PARALLELISM 12 BACKUP TYPE TO BACKUPSET;
run
{
crosscheck archivelog all;
BACKUP incremental level 0 as compressed backupset section size 20G DATABASE FORMAT '/rmanbkp/SLM_BKP/DBNAMEFULL_%U_%D_%M_%Y';
BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/rmanbkp/SLM_BKP/control_%d_%u_%s_%T';
}
oracle@Hostname:/rmanbkp/SLM_BKP$
112. re-org dynamic query for tablespace (not checked )
select '/* '||bytes/1024/1024/1024||' */ alter '||segment_type||' '||owner||'.'||segment_name||' move tablespace AD_TBS_TMP parallel 16;'
||chr(10)||
'alter '||segment_type||' '||owner||'.'||segment_name||' noparallel;'
from dba_segments where tablespace_name='AD_TBS'
order by bytes/1024/1024/1024 desc
/
113. To check Depencies of the objects
col owner format a20
col NAME format a20
col REFERENCED_OWNER format a30
col REFERENCED_NAME format a30
col REFERENCED_LINK_NAME format a30
set lines 200 pages 200
select owner,name,type,REFERENCED_OWNER,REFERENCED_NAME,REFERENCED_TYPE from dba_dependencies where owner='SCHEMA_NAME';
114. To check oracle_home path ,instead of checking oratab
cd /var/opt/oracle/
cat oratab
115. To check session is forwarding or not ,based on physical read
SQL> desc v$sess_io
Name Null? Type
------------------------------------- -------- ----------
SID NUMBER
BLOCK_GETS NUMBER
CONSISTENT_GETS NUMBER
PHYSICAL_READS NUMBER
BLOCK_CHANGES NUMBER
CONSISTENT_CHANGES NUMBER
OPTIMIZED_PHYSICAL_READS NUMBER
CON_ID NUMBER
SQL> select SID,BLOCK_GETS,CONSISTENT_GETS,PHYSICAL_READS,BLOCK_CHANGES,CONSISTENT_CHANGES,OPTIMIZED_PHYSICAL_READS from gv$sess_io where SID='1452';
116. To check temp tablespace how much consumed
select name,db_unique_name,TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024/1024 as tbs_size,FREE_SPACE/1024/1024/1024 as tbs_free,
round(((TABLESPACE_SIZE/1024/1024/1024)-(FREE_SPACE/1024/1024/1024))/(TABLESPACE_SIZE/1024/1024/1024),2)*100 as pct_full
from dba_temp_free_space a,v$instance,v$database;
117. To refresh mview (materialized view)
conn / as sysdba
set echo on
set time on
set timing on
set feedback on
spool spool_name.log
show user
select name,database_role,log_mode,open_mode from v$database;
execute DBMS_MVIEW.REFRESH('SCHEMA_NAME.MV_NAME','C');
spool off;
exit;
118. To check when last mv is refresh
select OWNER,MVIEW_NAME,last_refresh_date from all_mviews where owner='' and mview_name in ('');
119. To unlock and lock the object while doing stats gather
stats gather:
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'SCHEMA_NAME',TABNAME =>'Table_name',degree=> 20, CASCADE=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
unlock object:
exec dbms_stats.unlock_table_stats(OWNNAME => 'SCHEMA_NAME',TABNAME =>'Table_name');
lock object:
exec dbms_stats.lock_table_stats(OWNNAME => 'SCHEMA_NAME',TABNAME =>'Table_name');
120. To take ORACLE HOME backup
tar -cvf ORACLE_HOME_BKP1.tar /orahome12c/app/oracle/product/12.1.0.2/dbhome_1
121. To check oracle NO process related in os level
ps -ef|grep LOCAL=NO|grep -i DB_NAME
122. Delete archives without asking yes/no option (like prompt)
run
{
crosscheck archivelog all;
delete force noprompt archivelog all completed before 'sysdate-2';
delete force noprompt expired archivelog all;
}
123. Delete archives with prompt asking yes/no option
delete archivelog until time 'sysdate-2';
124. To delete archives for force fully.
delete force archivelog completed before 'sysdate-1';
125. To check particular archives available
list archivelog sequence ( no ) -------check archive exits or not
126. Connect DC to DR or DR to DC,using with service name
checkt the fal server name
show parameter fal_server
conn sys/password@targetservicename as sysdba
127. To Create user profile and assign to profile
SQL> create profile temp_profile limit password_reuse_max unlimited password_reuse_time unlimited;
Profile created.
SQL> alter user SCHEMA_NAME profile temp_profile;
;
128. Mrp start and stop command
Start:
alter database recover managed standby database disconnect from session;
Stop:
alter database recover managed standby database disconnect cancel;
**********************Thank You ***********************************
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select thread#,max(sequence#) from v$archived_log a,v$database b where a.resetlogs_change#=b.resetlogs_change# group by thread# order by thread#;
select thread#,max(sequence#) from v$log_history a,v$database b where a.resetlogs_change#=b.resetlogs_change# group by thread# order by thread#;
select distinct(checkpoint_time) from v$datafile_header;
02. To check active and inactive session details with sqlid, sid and machine.....
@sessions
Active :
col status for a9
col event for a30
col username for a10
col module for a25
col machine for a15
col osuser for a10
set lines 200 pages 50000
select inst_id, sid, serial#, sql_id,osuser, username, row_wait_obj#, event, module,machine,
round(last_call_et) LCE from gv$session s where username is not null
and status='ACTIVE'
order by inst_id, event, sql_id
/
Inactive :
col status for a9
col event for a30
col username for a10
col module for a25
col machine for a15
col osuser for a10
set lines 200 pages 50000
select inst_id, sid, serial#, sql_id,osuser, username, row_wait_obj#, event, module,machine,
round(last_call_et) LCE from gv$session s where username is not null
and status='INACTIVE'
order by inst_id, event, sql_id
/
03. To Check Long running session details in Database
@long_running_sessions
set lines 200 pages 8000
col serial# for 9999999
col machine for a20
col progress_pct for 99999999.00
col elapsed for a20
col remaining for a20
col opname for a30
SELECT s.inst_id,sl.opname,
s.sid,
s.serial#,s.status,
s.machine,sl.opname,
trunc(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
trunc(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining
FROM gv$session s,
gv$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial#
and sl.time_remaining>0
order by inst_id;
04. To check Particular session with query details,how running it's running the query.
@querydetails_check
set echo off
set serveroutput on size 50000
set verify off
set feedback off
accept inst_id prompt 'Enter instance id: '
accept SID prompt 'Enter sid: '
DECLARE
v_sid number;
vs_cnt number;
s sys.gv_$session%ROWTYPE;
p sys.gv_$process%ROWTYPE;
cursor cur_c1 is select sid from sys.gv_$process p,sys.gv_$session s where p.addr = s.paddr and sid = &SID and s.inst_id='&inst_id' and p.inst_id=s.inst_id;
BEGIN
dbms_output.put_line('=====================================================================');
select nvl(count(sid),0) into vs_cnt from sys.gv_$process p, sys.gv_$session s where p.addr = s.paddr and sid = &SID and s.inst_id='&inst_id' and p.inst_id=s.inst_id;
open cur_c1;
LOOP
FETCH cur_c1 INTO v_sid;
EXIT WHEN (cur_c1%NOTFOUND);
select * into s from sys.gv_$session where sid = v_sid and inst_id='&inst_id';
select * into p from sys.gv_$process where addr = s.paddr and inst_id='&inst_id';
dbms_output.put_line('INST_ID : '|| s.inst_id);
dbms_output.put_line('SID/Serial : '|| s.sid||','||s.serial#);
dbms_output.put_line('Foreground : '|| 'PID: '||s.process||' - '||s.program);
dbms_output.put_line('Shadow : '|| 'PID: '||p.spid||' - '||p.program);
dbms_output.put_line('Terminal : '|| s.terminal || '/ ' || p.terminal);
dbms_output.put_line('OS User : '|| s.osuser||' on '||s.machine);
dbms_output.put_line('Ora User : '|| s.username);
dbms_output.put_line('Details : '|| s.action||' - '||s.module);
dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Login Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
dbms_output.put_line('Last Call : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '9990.0') || ' min');
dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
dbms_output.put_line('Latch Spin : '|| nvl(p.latchspin, 'NONE'));
dbms_output.put_line('Current SQL statement:');
for c1 in ( select * from sys.gv_$sqltext where HASH_VALUE = s.sql_hash_value and inst_id='&inst_id' order by piece)
loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Previous SQL statement:');
for c1 in ( select * from sys.gv_$sqltext where HASH_VALUE = s.prev_hash_value and inst_id='&inst_id' order by piece)
loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Session Waits:');
for c1 in ( select * from sys.gv_$session_wait where sid = s.sid and inst_id='&inst_id')
loop
dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
end loop;
dbms_output.put_line('Connect Info:');
for c1 in ( select * from sys.gv_$session_connect_info where sid = s.sid and inst_id='&inst_id') loop
dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
end loop;
dbms_output.put_line('Locks:');
for c1 in ( select /*+ RULE */ decode(l.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.request) ) lrequest,
decode(l.type, 'MR', o.name,
'TD', o.name,
'TM', o.name,
'RW', 'FILE#='||substr(l.id1,1,3)||
' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) objname
from sys.gv_$lock l, sys.obj$ o
where sid = s.sid
and inst_id='&inst_id'
and l.id1 = o.obj#(+) )
loop
dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
end loop;
dbms_output.put_line('=====================================================================');
END LOOP;
close cur_c1;
exception
when no_data_found then
dbms_output.put_line('Unable to find process id &&SID for the instance number '||'&inst_id'||' !!!');
dbms_output.put_line('=====================================================================');
return;
when others then
dbms_output.put_line(sqlerrm);
return;
END;
/
undef SID
set heading on
set verify on
set feedback on
05. To check holder and waiter session in database.
@holder_waiter_session
set lines 200
col sess for a25
select inst_id,decode(request,0,'Holder: ','Waiter: ')|| sid sess,id1,id2,lmode, request, type from gv$lock
where (id1,id2,type)in(select id1,id2,type from gv$lock where request>0)
order by id1,request;
06. TO check Blocking session in database .
@blocking_session_details
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;
07. To Check Object locking details
@lock_details
set lines 200 pages 8000
col OBJECT_NAME for a30
SELECT a.INST_ID,/*+rule*/ a.sid,a.serial#, a.username,c.os_user_name,a.terminal,
b.object_id,substr(b.object_name,1,40) object_name
from gv$session a, dba_objects b, gv$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id;
08. To check tablespace details with pct size wise.
REM TABLESPACE DETAILS
col TABLESPACE_NAME for a30
REM Tablespace & freespace
set linesize 150
set pages 10000
SELECT tablespace_name,ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB,
ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,
ROUND(SUM(max_mb)) MAX_SZ_MB,
ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL
FROM (
SELECT tablespace_name, SUM(bytes)/1024/1024/1024 FREE_MB,
0 TOTAL_MB, 0 MAX_MB
FROM dba_free_space
GROUP BY tablespace_name
UNION
SELECT tablespace_name, 0 CURRENT_MB,
SUM(bytes)/1024/1024/1024 TOTAL_MB,
SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024/1024 MAX_MB
FROM dba_data_files
GROUP BY tablespace_name)
GROUP BY tablespace_name
order by 5;
9. To check how much time take query will time to complete.
*sofar*
col target for a30
col opname for a30
select username,sid,opname,target,sofar,totalwork,(sofar*100)/totalwork from v$session_longops where sofar<totalwork;
10. To connect pl/sql developer software without tns name
Database -- scan_name:scan_port/service_name
Database -- servicename or ip:port_number//
11. To check Ports numbers
Note : This is based on environment ,below ports is just exmaples :
show parameter remote_listener
srvctl config scan --> to find scan port and scan IP
srvctl config nodeapps -a --> to find virtual port and IP
FOR SCAN IP :1651
FOR Physical IP and Virtual IP : 1621
12. To check port open or not
telnet IP port_number --> command
which port we have to give scan port or vip port -------for SCAN ip give 1651 ( Note :Based on environment )
for vip and pysical is 1621 ( Note :Based on environment )
srvctl config scan --> to check scan name and ip
srvctl config scan_listener --> to check scan port
srvctl config nodeapps -a ---> to check vip name and IP
srvctl config listener or show parameter remote_listener --> to check vip port
\
13. To deleting the trace files,trim files and audti files in backup ground
nohup find . -name '*.trc' -mtime +3 -exec rm -r {} \; &
nohup find . -name '*.trm' -mtime +3 -exec rm -r {} \; &
nohup find . -name '*.aud' -mtime +2 -exec rm -r {} \; &
For Super Cluster server recomemded by ACS :
Current user below command update 10Feb20 -->
nohup find . -name '*.trm' -type f -print0 -mtime +2 | xargs -0 -n1 rm &
nohup find . -name '*.trc' -type f -print0 -mtime +2 | xargs -0 -n1 rm &
14. kill at os level related to database related
Note : don't use without proper knowing the output
ps -ef|grep LOCAL=NO|grep -i INVEXP|awk '{print "kill -9 "$2}' > kill.sh
15. To Take export meta data backup
expdp directory=EXP_MCLR_23jan19 dumpfile=EXP_schema_metadata_date_%U.dmp logfile=EXP_schema_metadata_date.log schemas=Schemaname content=metadata_only compression=all cluster=n metrics=y
16. To get ddl information of the Objects(procedure,package,dblink,table,materialized view etc...)
for proper format setting ---
set long 999999999 longchunksize 20000 pagesize 0 linesize 1000 --> this will give proper output
column ddl format a1000
set heading on or off
To get ddl information of the procedure:
set long 999999999
select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME','SCHEMA_NAME') from dual;
To get ddl metarailed view :
set long 999999999
select dbms_metadata.get_ddl('MATERIALIZED_VIEW','MV_NAME','SCAHEMA_NAME') from dual;
To get ddl information of the package and package body :
set long 999999999
select dbms_metadata.get_ddl('PACKAGE','PACKAGE_NAME','SCAHEMA_NAME') from dual;
SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','PACKAGE_NAME','SCAHEMA_NAME') FROM DUAL;
To get ddl information of the TRIGGER :
set long 999999999
SELECT DBMS_METADATA.GET_DDL('TRIGGER','TRIGGER_NAME','SCAHEMA_NAME') FROM DUAL;
To get ddl information of the DB link:
set long 999999999
SELECT DBMS_METADATA.GET_DDL('DB_LINK','DBLINK_NAME','SCAHEMA_NAME') FROM DUAL;
To get ddl information of the dba scheduler job data:
set long 999999999
select dbms_metadata.get_ddl('PROCOBJ','SCHEDULER_NAME','SCAHEMA_NAME') from dual;
17. To run .sh script in nohup
nohup sh /export/home/oracle/gatherstatics//_schemastats.sh &
18. To run .sql file script in nohup
nohup sqlplus /nolog < script_file_name.sql > script_nohup.log &
19. To run .rcv file in nohup
nohup rman target / <rman_script_file_name.rcv> rman_script_nohup.log &
20. To check nohup commands related output will display every 50 seconds
while true;do jobs;sleep 50;done
21. To Display limited number of files
ls -lrt log_[1-60]*.xml
22. connect to rman ( From primary to standy )
rman target sys/oracle@DRSERVICENAME
23. To kill particular session in database level
Syntax : alter system kill session 'SID,SERIAL#,@INSTANCENUMBER' immediate;
Ex:
alter system kill session '324,7879,@2' immediate;
24. To find stale stats and last analyzed for particular schema
stale --> yes means we have to run stats gather,no means no need.
select
table_name,
stale_stats,
last_analyzed
from
dba_tab_statistics
where
owner ='schema_name'
order by
last_analyzed desc, table_name asc;
25. To display (Dynamic query) grants from user to user for tables
SQL> select 'grant '||' '||'select'||' '||'on '||' '||'FROMUSER'||'.'||table_name ||' '||'to TOUSER;' from dba_tables where owner='FROMUSER';
26. To Copy command thread in asm mount point in grid user,inside the asmcmd
cp --port 1621 +DATA_R/CBSNGEOY/BACKUPSET/2016_10_14/nnndf0_TAG20161014T182103_0.962.925237267 sys/oracle@serverip.+ASM1:+DATA_P/BACKUP/2016_10_14/nnndf0_TAG20161014T182103_0
--for DBNAME--------
cp --port 1621 +DATA_DG/DBNAME/BACKUPSET/2016_10_20/annnf0_TAG20161020T163553_0.442.925749361 sys/oracle@serverip.+ASM1:+DATA_P/STAGE_RMAN/annnf0_TAG20161020T163553_0
27. To check unix mount point it will shows top consumed in top.
$du -sm *|sort -nr
28. To Set time in database level
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
29. To kill inactive,active with particular module session
select 'alter system kill session ''' || sid || ',' || serial# ||',@'||inst_id||''' immediate;' from gv$session where status='INACTIVE' and username='CARDS';
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from gv$session where status='INACTIVE' and username in ('');
select 'alter system kill session ''' || sid || ',' || serial# ||',@'||inst_id||''' immediate;' from gv$session where status='INACTIVE' and Module='PL/SQL Developer';
select 'alter system kill session ''' || sid || ',' || serial# ||',@'||inst_id||''' immediate;' from gv$session where status='INACTIVE' and Osuser='Logi Info .Net v4.0' and Module='w3wp.exe';
30. To kill particular with sql_id both nodes
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from gv$session where SQL_ID='274ksuz6zf2xr';
select 'alter system kill session ''' || sid || ',' || serial# ||',@'||inst_id||''' immediate;' from gv$session where sql_id='274ksuz6zf2xr';
31. To kill particular session related with db and os level ( example : select list query table)
SELECT distinct S.SID, S.SERIAL#, Q.SQL_TEXT
FROM V$SESSION S, V$SQL Qf
WHERE S.USERNAME IS NOT NULL
AND S.STATUS = 'ACTIVE'
AND S.SQL_ID IS NOT NULL
AND Q.SQL_ID = S.SQL_ID
AND Q.SQL_TEXT like ('SELECT%listquerytable%')
and username <> 'SYS';
select distinct '!kill -9 '||p.spid
||chr(10)||
'alter system kill session '''||s.sid||','||s.serial#||''' immediate;'
from v$session s, v$process p, V$SQL Q
where s.username <> 'SYS' and s.username is not null and s.SQL_ID IS NOT NULL
AND Q.SQL_ID = s.SQL_ID
AND Q.SQL_TEXT like ('SELECT%listquerytable%')
and s.paddr=p.addr
/
32. To view first ten files
ls -al|head 11
33. ASM disk space check
set lines 150 pages 200
select name,type,total_mb/1024,free_mb/1024,USABLE_FILE_MB/1024 from v$asm_diskgroup;
34. To check Tablescape space in database level.
col TABLESPACE_NAME for a30
REM Tablespace & freespace
set linesize 150
set pages 10000
SELECT tablespace_name,ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB,
ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,
ROUND(SUM(max_mb)) MAX_SZ_MB,
ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL
FROM (
SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,
0 TOTAL_MB, 0 MAX_MB
FROM dba_free_space
GROUP BY tablespace_name
UNION
SELECT tablespace_name, 0 CURRENT_MB,
SUM(bytes)/1024/1024 TOTAL_MB,
SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
FROM dba_data_files
GROUP BY tablespace_name)
GROUP BY tablespace_name
order by 5;
35. To check TEMP tablespace size and monitor purpose
TEMP Monitoring :
select name,db_unique_name,TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024/1024 as tbs_size,FREE_SPACE/1024/1024/1024 as tbs_free,
round(((TABLESPACE_SIZE/1024/1024/1024)-(FREE_SPACE/1024/1024/1024))/(TABLESPACE_SIZE/1024/1024/1024),2)*100 as pct_full
from dba_temp_free_space a,v$instance,v$database;
TEMP SPACE--------------------
select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024/1024,BLOCKS,STATUS,MAXBYTES/1024/1024/1024,MAXBLOCKS/1024/1024/1024 from dba_temp_files;
TEMP FREE SPACE ----------------------
select TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024/1024,ALLOCATED_SPACE/1024/1024/1024,FREE_SPACE/1024/1024/1024 from dba_temp_free_space;
TABLESPACE FREE SPACE
select FILE_NAME,BYTES/1024/1024/1024,STATUS from dba_data_files where tablespace_name='
select b.Total_MB,b.Total_MB - round(a.used_blocks*8/1024) Current_Free_MB,round(used_blocks*8/1024) Current_Used_MB,round(max_used_blocks*8/1024) Max_used_MB from v$sort_segment a,(select round(sum(bytes)/1024/1024) Total_MB from dba_temp_files ) b;
36. To check default temp tablespace
SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE'
37. To check top 20 consumed tablespace detail
SELECT grantee, privilege FROM dba_sys_privs where privilege like '%UNLIMITED%TABLE%' and grantee in (select username from dba_users where account_status='OPEN'and username not in ('SYS','SYSTEM','SYSMAN','GGS','DBSNMP','DBMON','DBSPI','OUTLN'));
38. To check datafile for particular tablespace releated
set line 160
set pagesize 500
col name for a70
col TABLESPACE_NAME for a25
select df.NAME,df.TABLESPACE_NAME,round(df.BYTES/1024/1024) Allocated,
round(nvl(dfs.BYTES/1024/1024,0)) FREE_SPACE, round(((df.BYTES/1024/1024)-nvl((dfs.BYTES/1024/1024),0))) USED_SPACE
from v$datafile_header df,
(select file_id,tablespace_name,sum(bytes) bytes
from dba_free_space
group by file_id,tablespace_name) dfs
where df.FILE#=dfs.FILE_ID (+)
AND df.TABLESPACE_NAME like upper('&Tablespace')
order by CREATION_TIME,free_space;
39. To Rebuild index using below command.
alter index schemaname.index_name rebuild online;
40. Rebuild index parallel (online) and no parallel (offline)
alter index CXPSADM.SYS_C0097820 rebuild parallel 8;
alter index CXPSADM.SYS_C0097820 rebuild online noparallel;
41. To compile invalid objects
Ex:
alter materialized view schemaname.MV_Name compile;
42. To Check database size
Physical size :
select sum(bytes/1024/1024/1024) Physical_size_gb from dba_data_files;
Actual size(logical size) :
select sum(bytes/1024/1024/1024) Actual_size_gb from dba_segments;
43. To Check RMAN Running backup in database
rman bkp status
Set line 2000
col end_time for a20
col start_time for a20
col time_taken_display for a20
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
time_taken_display,
input_bytes/1024/1024/1024,
output_bytes/1024/1024/1024
from V$RMAN_BACKUP_JOB_DETAILS
order by start_time;
44. To check the list of Privileges granted to a User (Grantee) :
Note : just for understanding :grantee --- means privilege taken and grantor privilege given
col Grantor for a12
col Grantee for a12
col Table_name for a12
set line 120
select GRANTEE, OWNER "OBJECT_OWNER", GRANTOR, TABLE_NAME, PRIVILEGE from DBA_TAB_PRIVS where GRANTEE='Schema_name';
45. To check the list of Roles granted to a User (Grantee) :
col Granted_role for a12
col Grantee for a12
set line 120
select GRANTEE, GRANTED_ROLE, ADMIN_OPTION from DBA_ROLE_PRIVS where GRANTEE=' ';
46: To check the System Privileges granted to a particular Role:
col Role for a12
set line 80
select ROLE, PRIVILEGE, ADMIN_OPTION from ROLE_SYS_PRIVS where ROLE=' ';
47. To check the Object Privileges granted to a particular Role:
col Role for a12
col Owner for a12
col Table_name for a12
col Column_name for a12
set line 120
select ROLE, OWNER "OBJECT_OWNER", TABLE_NAME, COLUMN_NAME, PRIVILEGE, GRANTABLE from ROLE_TAB_PRIVS where ROLE=' ';
48. To create DB link (DATABASE LINK)
conn Schema_name/Schema_name_pwd
create database link <dblink_name> connect to user identified by <user password> using '<database name>';
conn Schema_name/Schema_name#123
create database link DB_LINK_NAME connect to SCHEMA_NAME identified by pwd using 'service_name';
49. To check index ,related to particular schema and particular table.
select owner,segment_type,segment_name,sum(bytes)/1024/1024/1024 from dba_segments where segment_name in (select index_name from dba_indexes where table_name='FT_CORE' and table_owner='CXPSADM') group by owner,segment_type,segment_name
order by owner,segment_type,segment_name;
50. To Check How much RMAN RESTORE STATUS.
set lines 1500
set pages 100
col CLI_INFO format a10
col spid format a5
col ch format a20
col seconds format 999999.99
col filename format a65
col bfc format 9
col "% Complete" format 999.99
col event format a40
set numwidth 10
select s.inst_id, o.sid, CLIENT_INFO ch, context, sofar, totalwork,
round(sofar/totalwork*100,2) "% Complete"
FROM gv$session_longops o, gv$session s
WHERE opname LIKE 'RMAN%'
AND opname NOT LIKE '%aggregate%'
AND o.sid=s.sid
AND totalwork != 0
AND sofar <> totalwork;
Restore Status :
select inst_id, sid, CLIENT_INFO ch, seq#, event, state, wait_time_micro/1000000 seconds
from gv$session where program like '%rman%' and
wait_time = 0 and
not action is null;
51. To Creating new user/schema
CREATE USER Schema_name IDENTIFIED BY passpwd DEFAULT TABLESPACE tablespace_name TEMPORARY TABLESPACE TEMP;
GRANT connect TO Schema_name ;
GRANT RESOURCE TO Schema_name ;
52. To check owner of object
select object_name,object_type,owner from dba_objects where object_name=' ';
53. To check how much query remaining to execute in database level.
@sofar
col target for a30
col opname for a30
select username,sid,opname,target,sofar,totalwork,(sofar*100)/totalwork from v$session_longops where sofar<totalwork;
54. Fragmentation check query
set lines 9999
set pages 2000
select owner,table_name,
round(((blocks*8)/1024),2) "table size mb",
round((num_rows*avg_row_len/1024/1024),2) "actual data in table mb",
round(((blocks*8)/1024),2)- round((num_rows*avg_row_len/1024/1024),2) "wasted space mb",
((round(((blocks*8)/1024),2)- round((num_rows*avg_row_len/1024/1024),2)) / (round(((blocks*8)/1024),2)) ) *100 "percent fragmented"
from dba_tables
where blocks != 0
and owner = '&OWNER'
order by 1,4 desc
/
55. To check OEM Agent status (Oracle Entireprice Manager)
1-- ps -ef | grep oem
2--cd /u01/app/oracle/product/oem13c/agent13c/
3--cd agent_inst
4--cd bin
pwd----/u01/app/oracle/product/oem13c/agent13c/agent_inst/bin
run---- ./emctl status agent
./emctl pingOMS
56. To check running,holder session in database
running active sessions check:
ses.sql
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;
check holder waiter session :
hw.sql
set lines 200
col sess for a25
select inst_id,decode(request,0,'Holder: ','Waiter: ')|| sid sess,id1,id2,lmode, request, type from gv$lock
where (id1,id2,type)in(select id1,id2,type from gv$lock where request>0)
order by id1,request;
57. Error solution suggeted oraclesupport
[cssd(8020)]CRS-1603:CSSD error
through grid
crsctl stat res -t
ps -ef | grep d.bin
crsctl stat res -t -init
58. To check Running RMAN backup in database.
set linesize 150
set pagesize 300
col start_time for a20
col end_time for a20
col time_taken_display for a9
col output_bytes_display for a12
select input_type,
command_id,
status,
to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,
-- to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,
output_bytes_display,
time_taken_display
from v$rman_backup_job_details
--Where status = 'RUNNING'
--where COMMAND_ID like 'FULL%'
--where INPUT_TYPE like'DB%'
order by session_key desc;
58.1 rman backup check:
Set line 2000
col end_time for a20
col start_time for a20
col time_taken_display for a20
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yyyy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yyyy hh24:mi') end_time,time_taken_display,
input_bytes/1024/1024/1024,
output_bytes/1024/1024/1024
from V$RMAN_BACKUP_JOB_DETAILS
order by start_time;
59. To check backup is running on tape or device
set lines 200
set pages 2000
col TIME_TAKEN_DISPLAY for a10
col start_time for a20
col end_time for a20
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
col HOST_NAME for a20
select distinct i.instance_name,i.host_name,r.SESSION_KEY,b.DEVICE_TYPE, r.STATUS,r.START_TIME,r.END_TIME,r.time_taken_display,r.OUTPUT_BYTES/1024/1024 "SIZE_MB",
r.OUTPUT_BYTES/1024/1024/1024 "SIZE_GB",b.INCREMENTAL_LEVEL "INCR_LEV" from V$RMAN_BACKUP_JOB_DETAILS r,V$BACKUP_SET_DETAILS b,v$instance i where /*r.START_TIME >= SYSDATE-7 and */
r.SESSION_KEY=b.SESSION_KEY and b.INCREMENTAL_LEVEL is not null order by r.SESSION_KEY;
60. To check Running backup is incremental level 0 or 1 in tape
set lines 200
set pages 2000
col TIME_TAKEN_DISPLAY for a10
col start_time for a20
col end_time for a20
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
col HOST_NAME for a10
select distinct i.instance_name,r.SESSION_KEY,b.DEVICE_TYPE, r.STATUS,r.START_TIME,r.END_TIME,r.time_taken_display,r.OUTPUT_BYTES/1024/1024 "SIZE_MB",
r.OUTPUT_BYTES/1024/1024/1024 "SIZE_GB",b.INCREMENTAL_LEVEL "INCR_LEV" from V$RMAN_BACKUP_JOB_DETAILS r,V$BACKUP_SET_DETAILS b,v$instance i where --r.START_TIME >= SYSDATE-7 and
r.SESSION_KEY=b.SESSION_KEY and b.INCREMENTAL_LEVEL is not null order by r.SESSION_KEY;
61. To check audit table backup aud$
select count(*) from sys.aud$;
col owner format a20
col segment_name format a20
select sum(bytes/1024/1024),segment_name,owner from dba_segments where segment_name='AUD$' group by segment_name,owner;
62. To check locking table details in database
select object_name,s.sid,s.serial#,p.spid from v$locked_object i,dba_objects o,gv$session s,gv$process p where i.object_id=o.object_id and i.session_id=s.sid and s.paddr=p.addr;
63. To check locking object in particular table
set lines 200 pages 8000
col OBJECT_NAME for a30
SELECT a.INST_ID,/*+rule*/ a.sid,a.serial#, a.username,c.os_user_name,a.terminal,
b.object_id,substr(b.object_name,1,40) object_name
from gv$session a, dba_objects b, gv$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id;
64. How to run schema stats gather
spool schema_stats.log
set time on timing on echo on feedback on
EXEC DBMS_STATS.gather_schema_stats('schemaname_1',degree=>4,cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC DBMS_STATS.gather_schema_stats('schemaname_2',degree=>4,cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC DBMS_STATS.gather_schema_stats('schemaname_3',degree=>4,cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
spool off;
exit;
65. To Run schema stats gather in .sh file
cat /u02/slm/gatherstats/DBNAME_schemastats.sh
export ORACLE_HOME=/ora_home/app/product/12.1.0/dbhome_1
export ORACLE_SID=DBNAME2
export PATH=$PATH:$ORACLE_HOME/bin
TODAY=`date '+%d%m%Y_%H%M'`
$ORACLE_HOME/bin/sqlplus "/as sysdba" @/u02/slm/gatherstats/DBNAME_schemastats.sql
cp /u02/slm/gatherstats/log/DBNAME_schemastats.log /u02/slm/gatherstats/log/DBNAME_schemastats_"${TODAY}".log
exit;
cat /u02/slm/gatherstats/DBNAME_schemastats.sql
set time on
set timing on
set echo on
set feedback on
set lines 200
set pages 2000
spool /u02/slm/gatherstats/log/DBNAME_schemastats.log
exec DBMS_STATS.GATHER_schema_stats(OWNNAME => 'SCHEMANAME',degree=> 6, CASCADE=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
spool off;
exit;
To run:
nohup sh /u02/slm/gatherstats/DBNAME_schemastats.sh &
66. To run Table stats gather
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCHEMANAME',TABNAME =>'TABLE_NAME',degree=> 8, CASCADE=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCHEMANAME',TABNAME =>'TABLE_NAME',degree=> 8, CASCADE=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
67. To unlock the object while doing stats gather
exec dbms_stats.unlock_table_stats(OWNNAME => 'SCHEMA_NAME',TABNAME =>'TABLE_NAME');
exec dbms_stats.lock_table_stats(OWNNAME => 'SCHEMA_NAME',TABNAME =>'TABLE_NAME');
68. To Run dictionary&object stats of the database.
set echo off
set heading on
set feedback off
set termout off
set lines 400 pages 1000
conn / as sysdba
spool gather_stats_dic_object.log
exec dbms_stats.gather_fixed_objects_stats();
EXEC DBMS_STATS.gather_dictionary_stats;
spool off;
exit
69. To run .sql files in nohup mode in two ways givn in below.
1. connect without as sysdba :
nohup sqlplus /nolog < fixed_and_dict_stats.sql > fixed_and_dict_stats_nohup.log &
2. connect with as sysdba :
nohup sqlplus / as sysdba @scprod_procedure_16nov19.sql > scprod_procedure_16nov19_nohup_16nov19.log &
70. To divert the online services from one node to another node using below command.
srvctl relocate service -d HDPPRD -s HDPDB<service_name> -i HDPPRD1<current service instance_name> -t HDPPRD2<target instance_name>
71. To check gg log (golden gate)
gg check log
view report replicate_name
72. To set the defer and enable the log archive dest
Defer :
alter system set LOG_ARCHIVE_DEST_state_2='DEFER' sid='*';
Enable:
alter system set LOG_ARCHIVE_DEST_state_2='ENABLE' sid='*';
73. To check how much resotre is completed.
select SID,MBYTES_PROCESSED/1024 "MBYTES_PROCESSED",STATUS,OPERATION,INPUT_BYTES/1024/1024/1024 "INPUT_BYTES GB",OUTPUT_BYTES/1024/1024/1024 "OUTPUT_BYTES GB" from V$RMAN_STATUS where status='RUNNING';
74. To check scheduler job in db
col LAST_START_DATE format a30
col JOB_NAME format a15
col NEXT_RUN_DATE format a35
col LAST_RUN_DURATION format a35
select JOB_NAME,NEXT_RUN_DATE,ENABLED,LAST_START_DATE,LAST_RUN_DURATION,FAILURE_COUNT from dba_scheduler_jobs where job_name in ('JOB_NAME')
select
log_date,
job_name,
status,
run_duration,
ERROR#
from
dba_scheduler_job_run_details
where job_name in ('JOB_NAME');
set lines 200 pages 200
col job_name format a30
col stauts format a10
col log_date format a45
col RUN_DURATION format a15
select
log_date,
job_name,
status,
run_duration,
ERROR#
from
dba_scheduler_job_run_details
where job_name in ('JOB_NAME')
order by log_date asc;
75. To check Particular running Job Details
col owner format a10
col job_name format a30
col START_DATE format a35
set lines 200 pages 200
select owner,JOB_NAME,START_DATE,NEXT_RUN_DATE from all_scheduler_jobs where job_name in ('JOB_NAME'');
76. To check error related to job_scheduler
select
log_id,
to_char(log_date,'dd/mm/yyyy hh24:mi:ss') log_date,
substr(job_name, 1,20) job_name,
substr(status, 1,10) status,
additional_info operation
from
dba_scheduler_job_log
where
job_name='JOB_NAME'
order by
log_date ;
77. To check disks using kfod command
kfod disks=all dscvgroup=true status=true
78. To Check PSU patch in db(at os level)
oracle@hostname:…product/12.1.0/dbhome_1/OPatch$ ./opatch lsinventory| grep -i desc
ARU platform description:: Solaris Operating System (SPARC 64-bit)
Patch description: "OCW PATCH SET UPDATE 12.1.0.2.181016 (28259914)"
Patch description: "Database Bundle Patch : 12.1.0.2.181016 (28259867)"
Patch description: "Database PSU 12.1.0.2.170117, Oracle JavaVM Component (JAN2017)"
oracle@hostname:…product/12.1.0/dbhome_1/OPatch$ ./opatch lsinventory| grep -i applied
Patch 21065289 : applied on Thu Nov 22 18:00:39 IST 2018
Patch 20898351 : applied on Thu Nov 22 17:58:02 IST 2018
Patch 28259914 : applied on Thu Nov 22 16:43:36 IST 2018
Patch 28259867 : applied on Thu Nov 22 16:41:34 IST 2018
Patch 19339555 : applied on Tue Apr 11 15:54:29 IST 2017
Patch 24917972 : applied on Tue Mar 21 14:43:35 IST 2017
79. To Check how many archive's are generated hour base details
SELECT instance ins,
log_date "DATE" ,
lpad(to_char(NVL( COUNT( * ) , 0 )),6,' ') Total,
lpad(to_char(NVL( SUM( decode( log_hour , '00' , 1 ) ) , 0 )),3,' ') h00 ,
lpad(to_char(NVL( SUM( decode( log_hour , '01' , 1 ) ) , 0 )),3,' ') h01 ,
lpad(to_char(NVL( SUM( decode( log_hour , '02' , 1 ) ) , 0 )),3,' ') h02 ,
lpad(to_char(NVL( SUM( decode( log_hour , '03' , 1 ) ) , 0 )),3,' ') h03 ,
lpad(to_char(NVL( SUM( decode( log_hour , '04' , 1 ) ) , 0 )),3,' ') h04 ,
lpad(to_char(NVL( SUM( decode( log_hour , '05' , 1 ) ) , 0 )),3,' ') h05 ,
lpad(to_char(NVL( SUM( decode( log_hour , '06' , 1 ) ) , 0 )),3,' ') h06 ,
lpad(to_char(NVL( SUM( decode( log_hour , '07' , 1 ) ) , 0 )),3,' ') h07 ,
lpad(to_char(NVL( SUM( decode( log_hour , '08' , 1 ) ) , 0 )),3,' ') h08 ,
lpad(to_char(NVL( SUM( decode( log_hour , '09' , 1 ) ) , 0 )),3,' ') h09 ,
lpad(to_char(NVL( SUM( decode( log_hour , '10' , 1 ) ) , 0 )),3,' ') h10 ,
lpad(to_char(NVL( SUM( decode( log_hour , '11' , 1 ) ) , 0 )),3,' ') h11 ,
lpad(to_char(NVL( SUM( decode( log_hour , '12' , 1 ) ) , 0 )),3,' ') h12 ,
lpad(to_char(NVL( SUM( decode( log_hour , '13' , 1 ) ) , 0 )),3,' ') h13 ,
lpad(to_char(NVL( SUM( decode( log_hour , '14' , 1 ) ) , 0 )),3,' ') h14 ,
lpad(to_char(NVL( SUM( decode( log_hour , '15' , 1 ) ) , 0 )),3,' ') h15 ,
lpad(to_char(NVL( SUM( decode( log_hour , '16' , 1 ) ) , 0 )),3,' ') h16 ,
lpad(to_char(NVL( SUM( decode( log_hour , '17' , 1 ) ) , 0 )),3,' ') h17 ,
lpad(to_char(NVL( SUM( decode( log_hour , '18' , 1 ) ) , 0 )),3,' ') h18 ,
lpad(to_char(NVL( SUM( decode( log_hour , '19' , 1 ) ) , 0 )),3,' ') h19 ,
lpad(to_char(NVL( SUM( decode( log_hour , '20' , 1 ) ) , 0 )),3,' ') h20 ,
lpad(to_char(NVL( SUM( decode( log_hour , '21' , 1 ) ) , 0 )),3,' ') h21 ,
lpad(to_char(NVL( SUM( decode( log_hour , '22' , 1 ) ) , 0 )),3,' ') h22 ,
lpad(to_char(NVL( SUM( decode( log_hour , '23' , 1 ) ) , 0 )),3,' ') h23
FROM (
SELECT thread# INSTANCE ,
TO_CHAR( first_time , 'DD-MON-YY' ) log_date ,
TO_CHAR( first_time , 'hh24' ) log_hour
FROM v$log_history
)
GROUP BY INSTANCE ,
log_date
ORDER BY INSTANCE ,
to_date(log_date,'DD-MON-YY');
80. Dynamic Resize tablespace query (it will shows which tablespace we can resize )
set lines 168 pages 5000
SELECT
'/* '||to_char(CEIL((f.blocks-e.hwm)*(f.bytes/f.blocks)/1024/1024),99999999)||' Freeable MB */ ' ||'alter database datafile '''||file_name||''' resize '||CEIL(e.hwm*(f.bytes/f.blocks)/1024/1024)||'M;' DF_RESIZE FROM DBA_DATA_FILES f,
SYS.TS$ t,
(SELECT ktfbuefno relative_fno,ktfbuesegtsn ts#,
MAX(ktfbuebno+ktfbueblks) hwm FROM sys.x$ktfbue GROUP BY ktfbuefno,ktfbuesegtsn) e
WHERE
f.relative_fno=e.relative_fno and t.name=f.tablespace_name and t.ts#=e.ts#
and f.blocks-e.hwm > 1000
ORDER BY f.blocks-e.hwm DESC;
81. As per audit based on environment below lister should be on.
ADMIN_RESTRICTIONS_LISTENER = ON
ADMIN_RESTRICTIONS_LISTENER_SCAN1 = ON
ADMIN_RESTRICTIONS_LISTENER_SCAN2 = ON
ADMIN_RESTRICTIONS_LISTENER_SCAN3 = ON
82. To Check failed Login details ( wrong password hits while connecting to schema)
select USERID, userhost, decode(returncode,01017,'Login Error','Acount Locked') "ISSUE", spare1,
TO_CHAR ( CAST(
( FROM_TZ(
CAST(
TO_DATE(
TO_CHAR( ntimestamp# , 'DD/MM/YYYY HH:MI PM'),
'DD/MM/YYYY HH:MI PM'
)
AS TIMESTAMP
) ,
'GMT'
) AT LOCAL
)
AS TIMESTAMP)
, 'DD/MM/YYYY HH:MI PM') "Time",
sqltext,
comment$text from SYS.aud$
where ( returncode=1017 OR returncode=28000 )
and userid in 'SCHEMA_NAME'
order by ntimestamp# desc ;
83. Dynamic query to give all select privilege to all tables (from user to user)
spool /u02/slm/grants_<date>.sql
select 'grant '||' '||'select'||' '||'on '||' '||'FROM_USERNAME'||'.'||table_name ||' '||'to TO_USERNAME;'
from dba_tables where owner='FROM_USERNAME';
spool off
84. To Give only table,view grants from user to user
select 'grant select on '||owner||'.'||object_name||' to TO_USERNAME;' from dba_objects where owner='FROM_USERNAME' and object_type in ('TABLE','VIEW');
85. To give sys level privileges to User or grant resorce.
grant CREATE TRIGGER to <USERNAME>;
grant CREATE SEQUENCE to <USERNAME>;
grant CREATE TYPE to <USERNAME>;
grant CREATE PROCEDURE to <USERNAME>;
grant CREATE CLUSTER to <USERNAME>;
grant CREATE OPERATOR to <USERNAME>;
grant CREATE INDEXTYPE to <USERNAME>;
grant CREATE TABLE to <USERNAME>;
grant create session to <USERNAME>;
or
grant resource to Username;
86. To create logfile using rman (while taking rman prompt it will record like spool use of below command)
rman target / log=logfilename.txt
87. To Flush Shared Pool with particluar SQL ID:
Note : Don't use ,if we dont knw exact use of this command in production
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '9yc%';
ADDRESS HASH_VALUE
---------------- ----------
000000095FD77CF0 908321886
SQL> exec DBMS_SHARED_POOL.Schame_name1 ('000000095FD77CF0, 908321886', 'C');
88. As per oracle docs difference between dirrentail and cumulative backup.
A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0
A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0
89. To check how much restoration status
set lines 168 pages 5000
select SID,MBYTES_PROCESSED/1024 "MBYTES_PROCESSED",STATUS,OPERATION,INPUT_BYTES/1024/1024/1024 "INPUT_BYTES GB",OUTPUT_BYTES/1024/1024/1024 "OUTPUT_BYTES GB" from V$RMAN_STATUS where status='RUNNING';
90. To enable the audit( after using below command,we got updated details in locked account details.
SQL> audit session whenever not successful ;
Audit succeeded.
91. To revoke and grant the user
revoke select, update, insert on emp from slm;
revoke select, update, insert on emp from slm;
grant select, update, insert on emp to slm;
9. Export backup ,dump file storing different location in mount points.
[oracle@Hostname]$ cat /orabkp_slm/EXPBKP/script/expdp_DBNAME_full_cron.sh
[oracle@Hostname]$ cat /orabkp_slm/EXPBKP/script/expdp_DBNAME_full.sh
#!/bin/sh
export ORACLE_BASE=/oracle/app/product
export ORACLE_HOME=/oracle/app/product/11.2.0.4/db_home1
export ORACLE_SID=DBNAME
export DT=`date '+%d%m%Y_%H%M%S'`
export EXPFILE=expdp_DBNAME_`date '+%d%m%Y_%H%M%S'`
/oracle/app/product/11.2.0.4/db_home1/bin/expdp parfile=/orabkp_slm/EXPBKP/script/details.par dumpfile=EXP_BKP:${EXPFILE}_DIR1_%U.dmp,EXP_BKP1:${EXPFILE}_DIR2_%U.dmp,EXP_BKP2:${EXPFILE}_DIR3_%U.dmp,EXP_BKP3:${EXPFILE}_DIR4_%U.dmp directory=EXP_BKP logfile=expdp_DBNAME_$DT.log FULL=Y compression=all parallel=16
/usr/bin/find /orabkp_slm/EXPBKP/ -name 'expdp_DBNAME_*.dmp*' -mtime +1 -exec /bin/rm {} \;
/usr/bin/find /orabkp_slm/EXPBKP1/ -name 'expdp_DBNAME_*.dmp*' -mtime +1 -exec /bin/rm {} \;
/usr/bin/find /orabkp_slm/EXPBKP2/ -name 'expdp_DBNAME_*.dmp*' -mtime +1 -exec /bin/rm {} \;
/usr/bin/find /orabkp_slm/EXPBKP3/ -name 'expdp_DBNAME_*.dmp*' -mtime +1 -exec /bin/rm {} \;
/usr/bin/find /orabkp_slm/EXPBKP/ -name 'expdp_DBNAME_*.log' -mtime +15 -exec /bin/rm {} \;
##cd /orabkp_slm/EXPBKP/
##gzip expdp_DBNAME_*.dmp
93. To check crs log file location
example :
/asm_home/app/grid_base/diag/crs/Hostname/crs/trace
94. desc sys.user$ details
NAME – name for user or role
TYPE# – 0 for role or 1 for user
CTIME – the date of creation
PTIME – the date the password was last changed
EXPTIME – the date the password has last expired
LTIME – the date the resource was last locked
LCOUNT – number of failed logon
95. To check object is locked or not
SELECT B.Owner, B.Object_Name, A.Oracle_Usernam e, A.OS_User_Name
FROM V$Locked_Object A, All_Objects B
WHERE A.Object_ID = B.Object_ID;
96. To check whic object user is accessing
select * from gv$access where object='';
97. wallet not open ( this is related to environment issue,)while converting snapshot to physical ,
Note :simply set the database environment and check
take directly down.
Before taking up fire below command :
srvctl setenv database -d DBNAME -T ORACLE_UNQNAME=DBNAME
Take Db up and check status
On sql prompt :
set lines 168 pages 5000
col WRL_PARAMETER for a40
select * from gv$encryption_wallet order by 1;
98. explan plan for query
syntax : explain plan for "QUERYL_DETAILS";
select * from table (dbms_xplan.display);
99. Don't Ask any comment, not display any comment in query
SET DEFINE OFF
100. To check how many object present in schema
col owner format a30
select count(1),owner,status,object_type from dba_objects where owner='SCHEMA_NAME' GROUP BY owner,status,object_type;
101. To check expdp status (attach file in expdp)
Example :
[oracle@Hostname EXP_SCHEMA_NAME_schema 02:08:48]$ expdp attach=SYS_EXPORT_SCHEMA_02
Export: Release 12.1.0.2.0 - Production on Mon Oct 7 14:08:54 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Job: SYS_EXPORT_SCHEMA_02
Owner: SYS
Operation: EXPORT
Creator Privs: TRUE
GUID: 944D134E49C9751DE0532C99510AEC28
Start Time: Monday, 07 October, 2019 11:52:29
Mode: SCHEMA
Instance: SLM
Max Parallelism: 8
Timezone: +00:00
Timezone version: 18
Endianness: LITTLE
NLS character set: AL32UTF8
NLS NCHAR character set: AL16UTF16
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND /******** AS SYSDBA directory=expdp dumpfile=Exp_SLM_SCHEMA_NAME_schema_Date_%U.dmp logfile=Exp_SLM_SCHEMA_NAME_schema_Date.log schemas=SCHEMA_NAME compression=all parallel=8 cluster=n
COMPRESSION ALL
State: EXECUTING
Bytes Processed: 31,675,147,640
Percent Done: 84
Current Parallelism: 8
Job Error Count: 0
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_%u.dmp
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_01.dmp
bytes written: 4,096
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_02.dmp
bytes written: 5,163,536,384
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_03.dmp
bytes written: 5,696,098,304
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_04.dmp
bytes written: 5,348,511,744
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_05.dmp
bytes written: 4,792,729,600
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_06.dmp
bytes written: 5,654,609,920
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_07.dmp
bytes written: 5,028,229,120
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_08.dmp
bytes written: 37,781,504
Worker 1 Status:
Instance ID: 1
Instance name: SLM
Host name: Hostname
Process Name: DW00
State: WORK WAITING
102. TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]
The possible values have the following effects:
SKIP leaves the table as is and moves on to the next object. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.
APPEND loads rows from the source and leaves existing rows unchanged.
TRUNCATE deletes existing rows and then loads rows from the source.
REPLACE drops the existing table and then creates and loads it from the source. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.
103. In full expdp backup,exlude two schemas
expdp system/<password> directory=DATA_PUMP_DIR dumpfile=EXPDAT%U.DMP FULL=y exclude=schema:\"IN \(\'SCOTT\',\'SYSTEM\')\"
[oracle@Hostname]$ cat /orabkp_slm/EXPBKP/script/details.par_bkp_01102019
userid=' / as sysdba'
EXCLUDE=SCHEMA:"IN ('Schame_name1','Schame_name2')"
parfile=/orabkp_slm/EXPBKP/script/details.par_bkp_01102019 dumpfile=EXP_BKP:EXPDP_DIR1_%U.dmp,EXP_BKP1:EXPDP_DIR2_%U.dmp,EXP_BKP2:EXPDP_DIR3_%U.dmp,EXP_BKP3:EXPDP_DIR4_%U.dmp directory=EXP_BKP logfile=expdp_DBNAME_.log FULL=Y compression=all parallel=16
104. server to server scp command
nohup scp * oracle@10.0.0.143:/slm1/BKP_DETAILS/ &
105. To check db link details
col owner format a15
col db_link format a20
col username format a15
col host format a35
set lines 200 pages 200
select * from dba_db_links where owner in ('schemaname');
106. To find fragmentation of the tables
list of the tables to find fragmented tables :
select owner,table_name,
round(((blocks*8)/1024),2) "table size mb",
round((num_rows*avg_row_len/1024/1024),2) "actual data in table mb",
round(((blocks*8)/1024),2)- round((num_rows*avg_row_len/1024/1024),2) "wasted space mb",
((round(((blocks*8)/1024),2)- round((num_rows*avg_row_len/1024/1024),2)) / (round(((blocks*8)/1024),2)) ) *100 "percent fragmented"
from dba_tables
where blocks != 0
and owner in ('');
top listed tables and tablespace details :
select t.owner,t.table_name, s.tablespace_name,round(s.bytes/1024/1024) actual_MB,round(((t.num_rows+1) * t.avg_row_len)/1024/1024) optimal_MB,round(s.bytes/1024/1024) -round(((t.num_rows+1) * t.avg_row_len)/1024/1024) CLAIMABLE_MB from dba_tables t, dba_segments s where t.owner = s.owner and t.table_name = s.segment_name and round(s.bytes/1024/1024) - round(((t.num_rows+1) * t.avg_row_len)/1024/1024) > 50 order by CLAIMABLE_MB desc;
107. Details autentication service
#none --> db able to connect
#nts --> application able to connect
108. bin directory actual permssion
Executable Directory
# chmod 750 $ORACLE _HOME/bin ## -rwxr-x---
109. TO check modification of the table count like insert,update,delete etc
SELECT TABLE_OWNER,
TABLE_NAME,
INSERTS,
UPDATES,
DELETES,
TIMESTAMP AS `LAST_CHANGE`
FROM DBA_TAB_MODIFICATIONS
WHERE TO_CHAR(TIMESTAMP,'DD/MM/YYYY') = TO_CHAR(sysdate,'DD/MM/YYYY')
ORDER BY LAST_CHANGE DESC;`
110. Normal DB Health check
select name,open_mode,database_role from v$database;
alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
select startup_time from v$instance;
select distinct(checkpoint_time) from v$datafile_header;
select thread#,max(sequence#) from v$archived_log a,v$database b where a.resetlogs_change#=b.resetlogs_change# group by thread# order by thread#;
select thread#,max(sequence#) from v$log_history a,v$database b where a.resetlogs_change#=b.resetlogs_change# group by thread# order by thread#;
Check tablespace,asm level and mount point size.
111. How to take level 0 Backup
oracle@Hostname:/rmanbkp/SLM_BKP$ cat rman_L0_jan.rcv
CONFIGURE DEVICE TYPE DISK PARALLELISM 12 BACKUP TYPE TO BACKUPSET;
run
{
crosscheck archivelog all;
BACKUP incremental level 0 as compressed backupset section size 20G DATABASE FORMAT '/rmanbkp/SLM_BKP/DBNAMEFULL_%U_%D_%M_%Y';
BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/rmanbkp/SLM_BKP/control_%d_%u_%s_%T';
}
oracle@Hostname:/rmanbkp/SLM_BKP$
112. re-org dynamic query for tablespace (not checked )
select '/* '||bytes/1024/1024/1024||' */ alter '||segment_type||' '||owner||'.'||segment_name||' move tablespace AD_TBS_TMP parallel 16;'
||chr(10)||
'alter '||segment_type||' '||owner||'.'||segment_name||' noparallel;'
from dba_segments where tablespace_name='AD_TBS'
order by bytes/1024/1024/1024 desc
/
113. To check Depencies of the objects
col owner format a20
col NAME format a20
col REFERENCED_OWNER format a30
col REFERENCED_NAME format a30
col REFERENCED_LINK_NAME format a30
set lines 200 pages 200
select owner,name,type,REFERENCED_OWNER,REFERENCED_NAME,REFERENCED_TYPE from dba_dependencies where owner='SCHEMA_NAME';
114. To check oracle_home path ,instead of checking oratab
cd /var/opt/oracle/
cat oratab
115. To check session is forwarding or not ,based on physical read
SQL> desc v$sess_io
Name Null? Type
------------------------------------- -------- ----------
SID NUMBER
BLOCK_GETS NUMBER
CONSISTENT_GETS NUMBER
PHYSICAL_READS NUMBER
BLOCK_CHANGES NUMBER
CONSISTENT_CHANGES NUMBER
OPTIMIZED_PHYSICAL_READS NUMBER
CON_ID NUMBER
SQL> select SID,BLOCK_GETS,CONSISTENT_GETS,PHYSICAL_READS,BLOCK_CHANGES,CONSISTENT_CHANGES,OPTIMIZED_PHYSICAL_READS from gv$sess_io where SID='1452';
116. To check temp tablespace how much consumed
select name,db_unique_name,TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024/1024 as tbs_size,FREE_SPACE/1024/1024/1024 as tbs_free,
round(((TABLESPACE_SIZE/1024/1024/1024)-(FREE_SPACE/1024/1024/1024))/(TABLESPACE_SIZE/1024/1024/1024),2)*100 as pct_full
from dba_temp_free_space a,v$instance,v$database;
117. To refresh mview (materialized view)
conn / as sysdba
set echo on
set time on
set timing on
set feedback on
spool spool_name.log
show user
select name,database_role,log_mode,open_mode from v$database;
execute DBMS_MVIEW.REFRESH('SCHEMA_NAME.MV_NAME','C');
spool off;
exit;
118. To check when last mv is refresh
select OWNER,MVIEW_NAME,last_refresh_date from all_mviews where owner='' and mview_name in ('');
119. To unlock and lock the object while doing stats gather
stats gather:
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'SCHEMA_NAME',TABNAME =>'Table_name',degree=> 20, CASCADE=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
unlock object:
exec dbms_stats.unlock_table_stats(OWNNAME => 'SCHEMA_NAME',TABNAME =>'Table_name');
lock object:
exec dbms_stats.lock_table_stats(OWNNAME => 'SCHEMA_NAME',TABNAME =>'Table_name');
120. To take ORACLE HOME backup
tar -cvf ORACLE_HOME_BKP1.tar /orahome12c/app/oracle/product/12.1.0.2/dbhome_1
121. To check oracle NO process related in os level
ps -ef|grep LOCAL=NO|grep -i DB_NAME
122. Delete archives without asking yes/no option (like prompt)
run
{
crosscheck archivelog all;
delete force noprompt archivelog all completed before 'sysdate-2';
delete force noprompt expired archivelog all;
}
123. Delete archives with prompt asking yes/no option
delete archivelog until time 'sysdate-2';
124. To delete archives for force fully.
delete force archivelog completed before 'sysdate-1';
125. To check particular archives available
list archivelog sequence ( no ) -------check archive exits or not
126. Connect DC to DR or DR to DC,using with service name
checkt the fal server name
show parameter fal_server
conn sys/password@targetservicename as sysdba
127. To Create user profile and assign to profile
SQL> create profile temp_profile limit password_reuse_max unlimited password_reuse_time unlimited;
Profile created.
SQL> alter user SCHEMA_NAME profile temp_profile;
;
128. Mrp start and stop command
Start:
alter database recover managed standby database disconnect from session;
Stop:
alter database recover managed standby database disconnect cancel;
**********************Thank You ***********************************
Thank You