How to search a particular query in oracle ?

How to search a particular query in oracle ?

select table_name from dict where table_name like '%LINK%';

select table_name from dict where table_name like '%DIRECTORY%';

select table_name from dict where table_name like '%AUDIT%';

Database Monitoring

Database Monitoring 

Database and Instance Last start time:

SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM   sys.v_$instance;
SELECT SYSDATE-logon_time "Days", (SYSDATE-logon_time)*24 "Hours"
from  sys.v_$session where  sid=1;

Track Database Version:

SELECT * from v$version;

Track Database Name and ID information:

select dbid, name from v$database;

Track Database Global Name information:

select * from global_name;

Track Database Instance name:

SELECT INSTANCE_NAME FROM V$INSTANCE;‎

Track Database Host Details:

SELECT UTL_INADDR.GET_HOST_ADDRESS, UTL_INADDR.GET_HOST_NAME FROM DUAL;

Display information about database services:

select name,  network_name from  dba_services order by name;

Track Database Present Status:

SELECT created, RESETLOGS_TIME, Log_mode FROM V$DATABASE;

DB Character Set Information:

select * from nls_database_parameters;

Track Database default information:

select username, profile, default_tablespace, temporary_tablespace from dba_users;

Database startup shell script

Database startup shell script

vi startdb.sh

#!/bin/sh

#/etc/init.d/oracle start
##

lsnrctl start
${ORACLE_HOME}/bin/sqlplus / as sysdba <<END
startup
exit
END

Database shutdown shell script

Database shutdown shell script

vi stopdb.sh

lsnrctl stop
${ORACLE_HOME}/bin/sqlplus / as sysdba<<END
shutdown immediate
exit
END

PL/SQL (PROCEDURE,PACKAGE,TRIGGER & FUNCTION)

PL/SQL (PROCEDURE,PACKAGE,TRIGGER & FUNCTION)

To check all the procedures, packages, triggers and functions in the database.


SQL> select count(*),object_type from dba_objects where object_type in ('PROCEDURE','FUNCTION','PACKAGE','TRIGGER') group by object_type;

COUNT(*) OBJECT_TYPE
---------- -------------------
161 PROCEDURE
1320 PACKAGE
627 TRIGGER
305 FUNCTION

Procedure:

procedures are used to perform specific actions
pass values in and out by using an argument list
can be called from within other programs using the CALL command.

Example:

create or replace procedure test_procedure(dept number, name varchar2, loc varchar2)
as
begin
insert into scott.dept values(dept,name,loc);
end;
/

SQL> select * from scott.dept;

DEPTNO DNAME          LOC
---------- -------------- -------------
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON
50 FINANCE        INDIA

How to execute a procedure ?

exec test_procedure(50,'FINANCE','INDIA');

Query to see all procedures:

select object_name from dba_objects where object_type='PROCEDURE';


Packages:

packages are collections of functions and procedures
each package should consist of two objects:
*package specification
*package body

Built-in packages:

Oracle database comes with several built-in PL/SQL packages that provide:

-administration and maintain utilities
-extended functionality
-use to DESCRIBE command to view subprograms
-DBMS_STAT: gathering, viewing, and modifying optimizer statistics
-DBMS_OUTPUT:generating output from PL/SQL
-DBMS_SESSION:accessing the ALTER SESSION and SET ROLE statements
-DBMS_SHARED_POOL:managing the shared pool (for example flusing it)
-DBMS_UTILITY:getting time, CPU time and version information
-DBMS_SCHEDULER:scheduling functions and procedures that are callable from PL/SQL
-DBMS_REDEFINITION:redefining objects online
-UTL_FILE:reading and writing to operating system files from PL/SQL.

SQL> set serveroutput on --------To check the description or any error message
SQL> exec dbms_output.PUT_LINE('This is mqm message');
This is mqm message

How to execute to packages ?

SQL> set serveroutput on 
SQL> exec dbms_output.PUT_LINE('This is mqm message');

Query to see all packages?

select object_name from dba_objects where object_type='PACKAGES';


Triggers:

triggers are PL/SQL code objects that are stored in the database and that automatically run or "fire" when something happens. The oracle database allows many actions to serve as triggering events including an insert into a table, a user logging in to the database and someone trying to drop a table or change audit settings.


Function:

create or replace function compute_tax (salary number)
return number
as begin
if salary<5000 then
return salary*.15;
else
return salary*.33;
end if;
end;
/

How to execute a function ?

select sysdate from dual;
select compute_tax(4000) from dual;

Query to see to functions?

select object_name from dba_objects where object_type='FUNCTION';

What is Going on Inside My Database

What is Going on Inside My Database?

The simplest query to determine performance at database level is to query v$session_wait and take a lead from there.

sqlplus '/as sysdba'
SQL> select event, state, count (*) from v$session_wait group by event, state order by 3 desc;


It uses the Oracle wait interface to report what all database sessions are currently waiting and doing CPU activity.

Whenever there is an issue on Database System, like extremely slow log file writes this query will give good hint towards the cause of problem. Of course, just running couple of queries against wait interface doesn’t give you the full picture but nevertheless, if you want to see an instance sessions state overview, this is the simplest query you can use.

Interpreting this query output should be combined with reading some OS performance tool output (like vmstat or perfmon), to determine whether the problem is induced by CPU overload. For example, if someone is running a parallel backup compression job on the server which is eating all CPU time, some of these waits may be just a side-effect of CPU overload).

Sometimes you might want to exclude the background processes and idle sessions from the picture. On that scenario use the SQL Scripts provided below:

sqlplus '/as sysdba'
SET LINESIZE 200;
COL SW_EVENT FORMAT A90;
SQL> select count(*), CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END AS state, CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END AS sw_event FROM v$session WHERE type = 'USER' AND status = 'ACTIVE' GROUP BY CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END, CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END ORDER BY 1 DESC, 2 DESC;


Sometimes you might want to exclude the background processes and idle sessions from the picture. On that scenario use the SQL Scripts provided below:
This is something you get in ASH as well, instance performance graph which shows you the instance wait summary. ASH nicely puts the CPU count of server into the graph as well (that you would be able to put the number of “On CPU” sessions into perspective).

If you wish to include the background processes and idle sessions, use following query:

SQL> select count(*), CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END AS state, CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END AS sw_event FROM v$session_wait GROUP BY CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END, CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END ORDER BY 1 DESC, 2 DESC;

You can use similar technique for easily viewing the instance activity from other perspectives and dimensions as well, like which SQL is being executed.

SQL> select sql_hash_value, count(*) from v$session where status = 'ACTIVE' group by sql_hash_value order by 2 desc;
SQL> select sql_text,users_executing from v$sql where hash_value = <HashValue-PreviousCommand>;

High Archives Generated (Which cause performance issue)

High Archives Generated (Which cause performance issue) 

Whenever huge archives generates following are the points to check.

-Check hot backup is in progress or not.

If hot backup is running then moving archives to different mount that's only one solution

-Check after hot backup any tablespace was left in begin backup mode and failed to do end backup for any tablespace

-Check tbs in begin backup mode:

select a.tablespace_name
from sys.dba_data_files a, sys.v_$backup b
where b.status = 'ACTIVE'
and b.file# = a.file_id
group by a.tablespace_name;

Make that tablespace to end backup
eg:
alter tablespace end backup;

-Check which session is generating more redo.

rem -----------------------------------------------------------------------
rem Purpose: Transaction which generating more redo
rem This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed
rem by the session. High values indicate a session generating lots of redo.
rem Run the query multiple times and examine the delta between each occurrence
rem of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.
rem -----------------------------------------------------------------------

set pages 1000
set lines 140
SELECT s.sid, s.serial#, s.username, s.program,
i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc, 1, 2, 3, 4;

rem -----------------------------------------------------------------------
rem Purpose: Transaction which generating more redo
rem Run the query multiple times and examine the delta between each occurrence
rem of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by
rem the session.
rem -----------------------------------------------------------------------

SELECT s.sid, s.serial#, s.username, s.program,
t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;


Try to find the queries to find the sid, the related sql and the request which was causing the huge number of archives.

SELECT s.sid, s.serial#, s.username, s.program, t.used_ublk, t.used_urec FROM v$session s, v$transaction t
WHERE s.taddr = t.addr ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

The output of the query gives the sid, number of undo blocks used and Number of undo records used that were happening during that time.

From the sid we can get the request id and the request name.

select sql_text from v$sqltext where hash_value=( select prev_hash_value from v$session where sid='&sid');

select sid||' - '||serial#||' - '||osuser||' - '||username||' - '||machine||' - '||status||' - '||logon_time
from v$session where sid=&123;

with above query we can find the details and contact application team to stop the activity and schedule at non - peak hours of business.

How to disable Dataguard Broker

How to disable Dataguard Broker

On Primary:

Step.1

Verify Dataguard Configuration:

DGMGRL> show configuration
Configuration - dell
Protection Mode: MaxPerformance
Databases:
dell_live - Primary database
dell_stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

Disable fast-start failover:

DGMGRL> DISABLE FAST_START FAILOVER;
Disabled.

Disable Configuration:

DGMGRL> DISABLE CONFIGURATION;
Disabled.

Re-Verify Configuration:

DGMGRL> show configuration;
Configuration - dell
Protection Mode: MaxPerformance
Databases:
dell_live - Primary database
dell_stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED

Step.2

Stop DG Broker on Primary:

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DELL      READ WRITE           PRIMARY

SQL> show parameter DG_BROKER_START
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     TRUE

SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE;
System altered.

SQL> show parameter DG_BROKER_START
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE
SQL>

Step.3

Backup DB broker configuration files:

SQL> SHOW PARAMETER DG_BROKER
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1.dat
dg_broker_config_file2               string      /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2.dat
dg_broker_start                      boolean     FALSE

[oracle@rac1 dbs]$ cp dr1.dat dr1.dat_bkp
[oracle@rac1 dbs]$ cp dr2.dat dr2.dat_bkp

On standby:

Step.4

Stop DG Broker on Standby:

SQL> select name,open_mode,database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DELL      MOUNTED              PHYSICAL STANDBY

SQL> show parameter DG_BROKER_START
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     TRUE

SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE;
System altered.

SQL> show parameter DG_BROKER_START
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE

Step.5

Backup DB Broker Configuration Files:

SQL> SHOW PARAMETER DG_BROKER
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1.dat
dg_broker_config_file2               string      /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2.dat
dg_broker_start                      boolean     FALSE

[oracle@rac2 dbs]$ cp dr1.dat dr1.dat_bkp
[oracle@rac2 dbs]$ cp dr2.dat dr2.dat_bkp