Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

whtat is the Mostly Used commands and views in Oracle

This following Oracle commands and view are mostly used i in real time environment
For  Appache 
   #cd /usr/apache2/2.2/bin 
How to check the Status of appache server
#./apache status    ==> ./apachectl: line 94: lynx: not found 
How to  Stop the  appache server
/usr/apache2/2.2/bin/apachectl stop 
How to  Start the  appache server
/usr/apache2/2.2/bin/apachectl start 
check the location of appache server
/var/apache2/2.2/htdocs 
/usr/apache2/2.2/bin 
/usr/apache2/2.2/conf.d 

RabbitMQ :
Status==> 
cd /var/rabbitmq/rabbitmq_server-3.6.9/sbin/ 
rabbitmqctl status 

Start==> 
cd /var/rabbitmq/rabbitmq_server-3.6.9/sbin/ 
rabbitmq-server -detached 

ClaimsGW :
Stop==> 
kill pid 
mv claims-0.0.1-SNAPSHOT.jar backup/claims-0.0.1-SNAPSHOT.jar_date 
mv nohup.out nohup_date.out 
Start==> 
copy and paste the new jar to /export/home/deploy/claimsgw 
nohup java -jar claims-0.0.1-SNAPSHOT.jar & 
tail -f nohup.out 

How to check Total DATABase Size 
select 
(select sum(bytes)/1024/1024/1024 "data file" from dba_data_files)+ 
(select sum(bytes)/1024/1024/1024 "log file" from sys.v_$log)+ 
(select nvl(sum(bytes),0)/1024/1024/1024 "temp file" from dba_temp_files)+ 
(select sum(BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024/1024) "control file" from v$controlfile) "Total Database Size" from dual; 

How to 
Rebuild  oracle  Index 
select 'alter index ' || owner || '.' || 'index_name' || ' rebuild online;' from dba_indexes where owner='owner' and index_name not like '%$%'; 

How to check  Disk Group Size oracle 
SELECT name, free_mb/1024 free_GB, total_mb/1024 total_GB, free_mb/total_mb*100 as percentage, TYPE, USABLE_FILE_MB/1024 USABLE_GB FROM v$asm_diskgroup; 

what is Mostly used srvctl commands in oracle :
srvctl relocate database -db dbm01 -node sc01db02 
srvctl modify database -db dbm01 -server sc01db02,sc01db01 
srvctl add database -db dbm01 -server sc01db02,sc01db01 
More to know Server Control Utility (SRVCTL)

How to 
Check Invalid Objects in oracle and how to recompile that 
select * from dba_objects where status= 'INVALID' order by TIMESTAMP desc; 
Recompile It
select 'alter ' || OBJECT_TYPE || ' ' || OWNER ||'.'|| OBJECT_NAME || ' compile;' from dba_objects where status='INVALID' and owner='IMS_MASTXN_READ'; 
                                              (Or)
                         How to Recompiling Invalid Schema Objects in oracle

How to Use Rsync in oracle database
rsync -pavzh /root/rpmpkgs /tmp/backups/ 
-v : verbose
-r : copies data recursively (but don’t preserve timestamps and permission while transferring data
-a : archive mode, archive mode allows copying files recursively and it also preserves symbolic links, file permissions, user & group ownerships and timestamps
-z : compress file data
-h : human-readable, output numbers in a human-readable format

How to  Kill sessions if 
maximum number of processes exceeded  
 ORA-00020: maximum number of processes (1000) exceeded 

Check:
ps -ef | grep ora | wc -l 
How to kill oracle  sessions:
this following query is used to find the oracle inactive session's next query is used to kill the particular session
SELECT 'alter system kill session '''||s.sid ||','||s.serial#||''' immediate;' 
from v$session s 
where s.username is NOT NULL 
and s.type != 'BACKGROUND' 
    and s.username <> 'SYSTEM' 
and s.username <> 'SYS' 
    --and s.username ='IMS_GALTXN' 
    and s.status='INACTIVE'    
order by s.sid ; 

alter system kill session 'SID,SERIAL#' immediate;
How to check the oracle  Database files details
select name from v$datafile 
union 
select name from v$controlfile 
union 
select member from v$logfile 
union 
select name from v$tempfile; 
How to check Database Version of oracle :
select banner from v$version; 
Thread wise Instance details
Select instance from v$thread;

what do you want to check the 
before  Patch apply :
select * from dba_registry_sqlpatch; 
select * from dba_registry; 

$ unzip p9655017_10204_linux.zip 
$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir 9655017 
How to check the status of oracle Job Scheduler :
this following query gives the information of Job Scheduler'  OWNER,JOB NAME,JOB CREATOR,JOB TYPE,JOB ACTION,NUMBER OF ARGUMENTS,SCHEDULE TYPE, START DATE, REPEAT INTERVAL,ENABLED, STATE, LAST START DATE, LAST RUN DURATION, NEXT RUN DATE 
SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME=''; 
SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME=''; 

select OWNER,JOB_NAME,JOB_CREATOR,JOB_TYPE,JOB_ACTION,NUMBER_OF_ARGUMENTS,SCHEDULE_TYPE, START_DATE, REPEAT_INTERVAL,ENABLED, STATE, LAST_START_DATE, LAST_RUN_DURATION, NEXT_RUN_DATE from dba_scheduler_jobs where job_name='JOBNAME'; 
How to  ENABLE / DISABLE oracle job scheduler:
BEGIN      
DBMS_SCHEDULER.disable(name=>'"SCHEMA NAME"."JOB NAME"', force => TRUE);  
END; 

BEGIN 
DBMS_SCHEDULER.enable('"SCHEMA NAME"."JOB NAME"');  
end; 
How to get SQL Text query Using sql id:
select SQL_ID,SQL_TEXT from v$sql where SQL_ID='49u91r1cb7gm3'; 
How to check Modifiable parameter:
select ISSYS_MODIFIABLE FROM V$PARAMETER WHERE NAME IN ('LOG_BUFFER','DB_CACHE_SIZE','SHARED_POOL_SIZE','JAVA_POOL_SIZE','STREAMS_POOL_SIZE'); 
How to check Active & Inactive session Details in oracle database:
SELECT s.username,  s.status,  COUNT(1) 
FROM v$process p,  v$session s 
WHERE paddr(+)  =addr 
AND s.username IS NOT NULL 
GROUP BY s.status,  s.username 
ORDER BY s.status; 
How to Delete archive log before 2 days 
delete archivelog all completed before 'sysdate-2'; 

How to Check Oracle  Tablespace:
Select t.tablespace, t.totalspace as " Totalspace(MB)", round((t.totalspace-fs.freespace),2) as "Used Space(MB)", fs.freespace as "Freespace(MB)", 
round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) as "% Used", round((fs.freespace/t.totalspace)*100,2) as "% Free" from 
(select round(sum(d.bytes)/(1024*1024)) as totalspace, d.tablespace_name tablespace from dba_data_files d group by d.tablespace_name) t, 
(select round(sum(f.bytes)/(1024*1024)) as freespace, f.tablespace_name tablespace 
from dba_free_space f group by f.tablespace_name) fs where t.tablespace=fs.tablespace order by t.tablespace; 
SELECT df.tablespace_name "TABLESPACE",  totalusedspace "USED MB",  (df.totalspace - tu.totalusedspace) "FREE MB",  df.totalspace "TOTAL MB",  ROUND(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "PCT FREE" 
FROM  (SELECT tablespace_name,    ROUND(SUM(bytes) / 1048576) TotalSpace  FROM dba_data_files  GROUP BY tablespace_name  ) df, 
  (SELECT ROUND(SUM(bytes)/(1024*1024)) totalusedspace,    tablespace_name  FROM dba_segments  GROUP BY tablespace_name  ) tu 
WHERE df.tablespace_name = tu.tablespace_name 
ORDER BY df.tablespace_name; 
select sum(total_blocks*8)/1024/1024 "Total Size",sum(used_blocks*8)/1024/1024 "Used Size",sum(free_blocks*8)/1024/1024 "Free Size" from v$sort_segment; 
desc DBA_DATA_FILES; 
select FILE_NAME,TABLESPACE_NAME,STATUS, sum(BYTES/1024/1024) "Mb" from DBA_DATA_FILES group by tablespace_name,FILE_NAME,STATUS order by tablespace_name asc; 
SELECT tablespace_name, File_id, SUM(bytes/1024/1024)"Size (MB)" FROM DBA_FREE_SPACE group by tablespace_name, file_id order by tablespace_name asc; 
SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE'; 
desc dba_USERS; 
select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users; 
How to Switching Data File to a New Location using Rman: 
Assume that a disk fails, forcing you to restore a data file to a new disk location. After starting RMAN and connecting to the database as TARGET, you can use the SET NEWNAME command to rename the data file, then RESTORE to restore the missing data file. You run SWITCH to point the control file to the new data file and then RECOVER. This example allocates both disk and tape channels.
RUN 
{ 
  ALLOCATE CHANNEL dev1 DEVICE TYPE DISK; 
  ALLOCATE CHANNEL dev2 DEVICE TYPE sbt; 
  SQL "ALTER TABLESPACE users OFFLINE IMMEDIATE"; 
  SET NEWNAME FOR DATAFILE '/disk1/oradata/prod/users01.dbf' 
                        TO '/disk2/users01.dbf'; 
  RESTORE TABLESPACE users; 
  SWITCH DATAFILE ALL; 
  RECOVER TABLESPACE users; 
  SQL "ALTER TABLESPACE users ONLINE"; 
} 

How To Cloning Oracle Database 
select max(sequence#),thread# from gv$managed_standby group by thread#; 
select PROCESS,STATUS,THREAD#,SEQUENCE# from gv$managed_standby; 

select SEQUENCE# as Sequence_Number, to_char(first_time, 'DD-MON-YYYY hh24:MI:SS') "Applied(Last10)" from v$log_history where thread#=1 and sequence# > (select max(sequence# - 10) from v$log_history ); 

========================================= 
RESOURCE MANAGER 
========================================= 
NAME                                 TYPE        VALUE 
------------------------------------ ----------- ------------------------------ 
resource_limit                       boolean     TRUE 
resource_manager_cpu_allocation      integer     144 
resource_manager_plan                string      SCHEDULER[0x446C]:DEFAULT_MAIN 
                                                 TENANCE_PLAN 

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ''; 
Active Session History (ASH)
Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized.
If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value.
Currently, ASH size is 67108864 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:
select total_size,awr_flush_emergency_count from v$ash_info; 
================================================================================
Database Backup and Recovery Basics   
How to Delete 
Oracle Old Obsolete and Expired  RMAN Backup 

Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL Sequence

Postgresql maximum size

How to configure Replication Manager (repmgr) ?

PostgreSQL pgBadger