Oracle Database Scripts : July 2023

Sunday, July 30, 2023

check Oracle database slow SQL Response

SET LINESIZE 200 PAGESIZE 50000

COL INST_ID FORMAT 999

COL "Response_Time (msecs)" FORMAT 999,999,999,999.99

COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17

SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI') BEGIN_TIME,
TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI') END_TIME,
INST_ID,
ROUND (VALUE * 10, 2) "Response_Time (msecs)"
FROM GV$SYSMETRIC
WHERE     1 = 1
AND METRIC_NAME = 'SQL Service Response Time'
ORDER BY INST_ID;

BEGIN_TIME        END_TIME          INST_ID Response_Time (msecs)                                                                                                                                       
----------------- ----------------- ------- ---------------------                                                                                                                                       

15-JUL-2023 01:05 15-JUL-2023 01:06       1                   .44
15-JUL-2023 01:04 15-JUL-2023 01:05       1                   .49
15-JUL-2023 01:03 15-JUL-2023 01:04       1                   .59
15-JUL-2023 01:02 15-JUL-2023 01:03       1                   .52
15-JUL-2023 01:01 15-JUL-2023 01:02       1                   .45
15-JUL-2023 01:00 15-JUL-2023 01:01       1                  1.07
15-JUL-2023 00:59 15-JUL-2023 01:00       1                   .58

You can also query for the minimum, average and maximum response time for the last minute:

SET LINESIZE 200 PAGESIZE 50000
COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17
COL INST_ID FORMAT 999
COL "Min Response Time (msecs)" FORMAT 999,999,999,999.99
COL "Avg Response Time (msecs)" FORMAT 999,999,999,999.99
COL "Max Response Time (msecs)" FORMAT 999,999,999,999.99

  SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI') BEGIN_TIME,
         TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI') END_TIME,
         INST_ID,
         ROUND (MINVAL * 10, 2) "Min Response Time (msecs)",
         ROUND (AVERAGE * 10, 2) "Avg Response Time (msecs)",
         ROUND (MAXVAL * 10, 2) "Max Response Time (msecs)"
    FROM GV$SYSMETRIC_SUMMARY
   WHERE     1 = 1
         AND METRIC_NAME = 'SQL Service Response Time'
ORDER BY INST_ID;

BEGIN_TIME        END_TIME          INST_ID Min Response Time (msecs) Avg Response Time (msecs) Max Response Time (msecs)
----------------- ----------------- ------- ------------------------- ------------------------- -------------------------
30-JUL-2023 20:12 30-JUL-2023 21:13       1                       .00                     17.65                     40.38

SET LINESIZE 200 PAGESIZE 50000
COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17
COL INST_ID FORMAT 999
COL "Response Time (msecs)" FORMAT 999,999,999,999.99

  SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI') BEGIN_TIME,
         TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI') END_TIME,
         INSTANCE_NUMBER INST_ID,
         ROUND (VALUE * 10, 2) "Response Time (msecs)"
    FROM DBA_HIST_SYSMETRIC_HISTORY
   WHERE 1 = 1 AND METRIC_NAME = 'SQL Service Response Time'
ORDER BY BEGIN_TIME DESC, INSTANCE_NUMBER;

BEGIN_TIME        END_TIME          INST_ID Response Time (msecs)                                                                                                                                       
----------------- ----------------- ------- --------------------- 
15-JUL-2023 01:03 15-JUL-2023 01:04       1                   .59
15-JUL-2023 01:02 15-JUL-2023 01:03       1                   .52
15-JUL-2023 01:01 15-JUL-2023 01:02       1                   .45
15-JUL-2023 01:00 15-JUL-2023 01:01       1                  1.07
15-JUL-2023 00:59 15-JUL-2023 01:00       1                   .58

SET LINESIZE 200 PAGESIZE 50000
COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17
COL INST_ID FORMAT 999
COL "Response Time (msecs)" FORMAT 999,999,999,999.99

  SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI') BEGIN_TIME,
         TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI') END_TIME,
         INSTANCE_NUMBER INST_ID,
         ROUND (VALUE * 10, 2) "Response Time (msecs)"
    FROM DBA_HIST_SYSMETRIC_HISTORY
   WHERE 1 = 1 AND METRIC_NAME = 'SQL Service Response Time'
ORDER BY BEGIN_TIME DESC, INSTANCE_NUMBER;

SET LINESIZE 200 PAGESIZE 50000
COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17
COL INST_ID FORMAT 999
COL "Min Response Time (msecs)" FORMAT 999,999,999,999.99
COL "Avg Response Time (msecs)" FORMAT 999,999,999,999.99
COL "Max Response Time (msecs)" FORMAT 999,999,999,999.99

  SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI') BEGIN_TIME,
         TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI') END_TIME,
         INSTANCE_NUMBER INST_ID,
         ROUND (MINVAL * 10, 2) "Min Response Time (msecs)",
         ROUND (AVERAGE * 10, 2) "Avg Response Time (msecs)",
         ROUND (MAXVAL * 10, 2) "Max Response Time (msecs)"
    FROM DBA_HIST_SYSMETRIC_SUMMARY
   WHERE 1 = 1 AND METRIC_NAME = 'SQL Service Response Time'
ORDER BY BEGIN_TIME DESC, INSTANCE_NUMBER;

grep commands patterns and metacharacter classes In Linux escape "\" character importance

cat /etc/oratab | grep -e "^[\a-Az-Z0-9\]\{0,8\}\:*/[[:graph:]]*"

Hints:-

Always use caret "^" and "$" without escape "\" like "^[[:digit:]]" or "$[[:digit:]]" .

To include forward search use "\+" or "*" like "^[[:graph:]]\+" or "^[[:digit:]]*" .

unzip and zip files and tar files in linux

unzip -o /path/to/source_zip_file.zip -d /path/to/destination

-o :- source_directory

-d :- destination_directory

unzip

-o /u01/software/LINUX.X64_19300_GRID_HOME.zip

-d /u01/app/grid

Saturday, July 29, 2023

OPATCH, OPATCHAUTO AND AUTOUPGRADE.JAR usage In Oracle Database

Opatch :-

a java based utility enables the application and rollback patches oracle software.

location:- $ORACLE_HOME/OPatch/

----------------------------------------------------------------------------------------------------------------

opatch prereq CheckSystemPatch -phBaseDir <path_to_patch>
opatch apply <path_to_patch>

opatchauto apply <path_to_patch> -oh <grid_oracle_home> -ocmrf <path_to_ocm_response_file>

opatch lsinventory

opatch lsinventory -patch_id

opatch rollback -id <patch_id>

opatch lsinventory | grep <patch_id>

opatch prereq CheckConflictAmongPatches -phBaseDir <path_to_patch>

------------------------------------------------------------------------------------------------------

Opatchauto :-

orchestration tool generates patching instruction specific to your target configuration then uses opatch to perform patching without user intervention.

location:- $GRID_HOME/OPatch/opatchauto

-----------------------------------------------------------------------------------------

opatchauto apply <path_to_patch> -analyze

opatchauto apply <path_to_patch>

Rolling Patch Apply :-

opatchauto apply <path_to_patch> -oh <grid_oracle_home> -ocmrf <path_to_ocm_response_file>

opatchauto apply <path_to_patch> -verify

opatchauto lspatches

opatchauto rollback -id <patch_id>

opatchauto lspatch <patch_id>

opatchauto prereq CheckConflictAmongPatches <path_to_patch>

--------------------------------------------------------------------------------------------------

autoupgrade.jar :-

automate upgrade of oracle database.

1) first install latest version oracle db software

2) start

location:- $ORACLE_HOME/jlib/autoupgrade.jar

cat config.properties

----------------------------------------------------------------------------------

# Source database details

source_home=/path/to/source/oracle_home

source_sid=SOURCE_SID

source_user=SYS

source_password=source_sys_password

source_sysdba_password=source_sysdba_password

# Target database details

target_home=/path/to/target/oracle_home

target_version=19.0.0

# AutoUpgrade behavior

auto_pfile=TRUE

compatible=19.0.0

timezone_upg=yes

datapatch=yes

upgrade_parallelism=4

# Log file locations

log_dir=/path/to/log/directory

log_file=autoupgrade.log

# Optional parameters

parameters=statistics_level=typical

---------------------------------------------------------------------------------------------------

java -jar autoupgrade.jar -config <path_to_config_file> -mode readyness

java -jar autoupgrade.jar -config <path_to_config_file> -mode prechecks

java -jar autoupgrade.jar -config <path_to_config_file> -mode analyze

Apply upgrades:-

java -jar autoupgrade.jar -config <path_to_config_file> -mode deploy

java -jar autoupgrade.jar -config <path_to_config_file> -mode fixups

java -jar autoupgrade.jar -config <path_to_config_file> -mode report

---------------------------------------------------------------------------------------------------

++++++++++++ Always use latest opatch from oracle support websites and Export Oracle environments before doing anything ++++++++++++++++++

always do after autoupgrades/ patching

$ORACLE_HOME/OPatch/datapatch verbose

RAC SRVCTL & CRSCTL

Stop DB

sudo -iu oracle

default :- srvctl stop database -d PUNEDB ---------> will stop database on all nodes

srvctl stop database -d PUNEDB -o normal

srvctl stop database -d PUNEDB -o immediate

srvctl stop database -d PUNEDB -o transactional

srvctl stop database -d PUNEDB -o abort

START DB

sudo -iu oracle

default :- srvctl start database -d PUNEDB ----------------> will start database on all nodes

srvctl start database -d PUNEDB -o nomount

srvctl start database -d PUNEDB -o mount --------> for standby db, backup task

srvctl start database -d PUNEDB -o open ---------> default behaviour

STOP / START INSTANCE

sudo -iu oracle

srvctl stop instance -d PUNEDB -i PUNE1

srvctl start instance -d PUNEDB -i PUNE2

To Stop CLUSTERWARE / CRS :-

sudo -iu root

ps -ef | grep -i d.bin

cd ~/bin

~/bin # ./crsctl disable crs

~/bin # ./crsctl stop crs -f

To Start CLUSTERWARE /CRS :-

sudo -iu root

ps -ef | grep -i d.bin

cd ~/bin

~/bin # ./crsctl enable crs

~/bin # ./crsctl start crs

Friday, July 28, 2023

Start & Stop MRP on Standby database

Stop log apply service (MRP) on standby :-

alter database recover managed standby database cancel;

shutdown immediate;

stop log shipping from primary and shutdown primary database:-

Note:- Run this on primary--> to get log_archive_dest location for standby db's please use below query:-

col dest_name for a30
col status for a10
col destination for a50
select dest_name,status,destination from gv$archive_dest_status where destination is not null;

then :-

show parameter log_archive_dest_state_&n; ------> n is number from above query

alter system set log_archive_dest_state_&n='DEFER';

shutdown immediate;

To start primary database & enable log shipping use below steps:-

steps to be performed on PRIMARY DB:-

startup;

alter system set log_archive_dest_state_&n='ENABLE';

steps to be performed on STANDBY DB:-

startup nomount;

alter database mount standby database;

alter database recover managed standby database disconnect from session;

--------- to check status of mrp on standby and (lgwr or lns) on Primary -----

on standby:-

SQL>

-------------------------- Final all in one query for MRP DR ARCHIVE LOG GAP --------------

select a.thread# thread,
k.host_name,
k.instance_name,
m.process,
m.status,
a.arch_seq arch,
b.appl_seq appl,
(a.arch_seq-b.appl_seq)
difference from (select thread#, max(sequence#)
as arch_seq from gv$archived_log where archived = 'YES' group by thread#) a,
(select thread#, max(sequence#) as appl_seq from gv$archived_log where applied = 'YES' group by thread#) b,gv$instance k,gv$managed_standby m
where a.thread# = b.thread# (+) and b.thread# = k.thread# (+) and m.thread#<>0 and m.process like '%MRP%' order by a.thread#, a.arch_seq, b.appl_seq;

----------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------
THREAD HOST_NAME INSTANCE_NAME PROCESS STATUS ARCH APPL DIFFERENCE
---------- ---------------------------------------------------------------- ---------------- --------- ------------ ---------- ---------- ----------
1 host1 instance1 MRP0 APPLYING_LOG 418336 418336 0
2 host2 instance2 MRP0 APPLYING_LOG 512681 512681 0
3 host3 instance3 MRP0 APPLYING_LOG 564134 564134 0
4 host4 instance4 MRP0 APPLYING_LOG 549583 549583 0

**************** The Best Option is Search For Oracle Support Help ***************************************

OPEN STANDBY DB IN READONLY (ACTIVE DATAGUARD)

1. check status of dataguard

select name,open_mode from gv$dataguard;

NAME OPEN_MODE

----------------------------------

PUNE MOUNTED

2. Cancel MRP on standby db

alter database recover managed standby database cancel;

3. Open standby db in readonly mode:-

Hint:- db should be in mount mode already.

alter database open read only;

4. Start MRP with real-time log apply on standby db:-

alter database recover managed standby database using current logfile disconnect from session;

5. Verify the standby db status:-

select name, open_mode from gv$database;

NAME OPEN_MODE

---------------------------------------------------

PUNE READ ONLY WITH APPLY

6. Check MRP process is running :-

select process, status, sequence# from gv$managed_standby where process like '%MRP%';

PROCESS STATUS SEQUENCE#

----------------------------------------------------

MRP0 APPLYING LOG 2000

=========================================================================

************************** If you need to do manual recovery ********************************

**copy archive logs from primary db to standby db and perform below steps:-

alter database recover managed standby database cancel;

alter database register logfile '/u01/oradata/redo03.log';

alter database recover managed standby database using current logfile disconnect from session;

alter database recover managed standby database disconnect from session;

check gap :-

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE#

FROM V$ARCHIVE_GAP;

Monday, July 24, 2023

automation of listener and database and RMAN

For Listener must start at reboot automatically:-

[oracle@kayyum ~]$ cat start_listener.sh
#!/bin/sh
# we will add below environment variable of oracle db
export ORACLE_HOME=/u02/app/oracle/product/19c/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
# we will trigger listener command
lsnrctl start

[oracle@kayyum ~]$ crontab -l

@reboot /u02/app/oracle/product/19c/dbhome_1/bin/dbstart > /dev/null 2>&1

@reboot /home/oracle/start_listener.sh > /dev/null 2>&1

#!/bin/sh

export ORACLE_HOME=/u02/app/oracle/product/19c/dbhome_1

export ORACLE_SID=emcdb
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

rman target \/ << EOF > rman_$ORACLE_SID_$(DATE).log

run

{

alter system checkpoint;

backup database format '/path/to/backup/location/%d_%t_%U';

}

EOF

alter system suspend & alter system resume In Oracle Database

If the ALTER SYSTEM SUSPEND statement is entered on one system in an Oracle RAC configuration, then the internal locking mechanisms propagate the halt request across instances, thereby suspending I/O operations for all active instances in a given cluster."

10:41:18 SQL> select database_status from v$instance;
DATABASE_STATUS
-----------------

SUSPENDED
Elapsed: 00:00:00.01
10:41:26 SQL>
10:41:27 SQL>
10:41:28 SQL>
10:41:28 SQL> alter system resume;
System altered.
Elapsed: 00:00:00.63
10:41:34 SQL>
10:41:35 SQL>
10:41:35 SQL> select database_status from v$instance;
DATABASE_STATUS
-----------------
ACTIVE
Elapsed: 00:00:00.01
10:41:43 SQL>

Sunday, July 23, 2023

Interactive copy on select HTML PAGE

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>On Select Copy To Clipboard Using JavaScript</title>

<style>
body{
display: flex;
flex-direction: column;
align-items: center;
margin-top: 200px;
}
input{
box-shadow: 0 0 5px;
}
pre{
box-shadow: 0 0 5px;
}
textarea{
white-space: pre-wrap;
word-wrap: break-word;
height: auto;
width: auto;
resize: none;
overflow: auto;
box-shadow: 0 0 5px;
}

.container{
display: flex;
flex-direction: column;
flex-wrap: wrap;
align-items:center;
gap: 10px;
border: 2px solid gold;
margin: 10px;
padding: 10px;
background-color: antiquewhite;
}

.reload{
height: 25px;
width: 100px;
box-shadow: 0 0 5px;
}

.output{
white-space: pre-wrap;
word-wrap: break-word;
height: auto;
width: auto;
resize: both;
}
</style>
</head>
<body>
<div class="container">

<input type="file" />

<button class="reload">Refresh Page</button>


<textarea class="input" readonly placeholder="input file content"></textarea>


<pre class="output" ></pre>


<textarea class="output1" placeholder="test paste here"></textarea>
</div>
</body>

<script>
// addeventlistener select event
document.querySelector('.input').addEventListener('select',(e)=>{
let selection = e.target.value.substring(
e.target.selectionStart,
e.target.selectionEnd
);

// assign selection content to pre
document.querySelector('pre').textContent = selection;
let pre = document.querySelector('pre').textContent;

// copy pre tag content to navigator api clipboard
navigator.clipboard.writeText(pre).then(()=>{
alert("copied to clipboard !");
});
});

// open file
document.querySelector('input').addEventListener('change',function(){
// filereader constructor
let filereader = new FileReader();
//function for onload event
filereader.onload = function(){
//output location 'textarea'
document.querySelector('.input').value = filereader.result;
}

// readastext()
filereader.readAsText(this.files[0]);

//screen width height
let width = document.querySelector('.input').innerHTML+screen.width;
let height = document.querySelector('.input').innerHTML+screen.height;
// resize textarea for content
document.querySelector('.input').style.width = `${width-100}px`;
document.querySelector('.input').style.height = `${height}px`;

});

// reload page button
document.querySelector('.reload').addEventListener('click',()=>{
window.location.reload();
});
//outpu1 box
document.querySelector('.output1').addEventListener('input',(e)=>{
//screen width height
let width = document.querySelector('.output1').innerHTML+screen.availWidth;
let height = document.querySelector('.output1').innerHTML+screen.availHeight;
// resize textarea for content
document.querySelector('.output1').style.width = `${width-100}px`;
document.querySelector('.output1').style.height = `${height}px`;
});
</script>
</html>

Saturday, July 22, 2023

Why we perform "alter system switch logfile/alter system archive log current (RAC) and alter system checkpoint "

alter system switch logfile;

switch the logfile in the current instance so log sequence move to another

number by executing the command without waiting to full the logfile and

archiving it. forcing it also switches checkpoint.

alter system archive log current;

this forces all redo log files to get archived.

It performs the switch and archiving both.

RAC DB it forces global switch and archiving both in all instances.

for only a thread : since one db instances can contain only one thread....

alter system archive log current thread 1;

alter system checkpoint;

it makes change in checkpoint but not switch redo log file.

it makes current active redo log to inactive so that we can drop the redo log sequence.

alter system checkpoint;

LOG SWITCH "alter system switch logfile (STANDALONE) / alter system archive log current (RAC DB) ------> triggers checkpoint

CHECKPOINT "alter system checkpoint" ----------------> does not trigger LOG SWITCH

select first_time,first_change#,sequence#,status from v$log;

select checkpoint_change#,checkpoint_time from v$datafile_header;

alter system switch logfile;

alter system checkpoint;

alter system archive log current;

ALTER SYSTEM SWITCH LOGFILE;: Manually switches the current redo log file to the next available log file.
ALTER SYSTEM CHECKPOINT;: Forces a checkpoint, ensuring modified data in the buffer cache is written to data files.
ALTER SYSTEM ARCHIVE LOG CURRENT;: Manually archives the current redo log file, making it available for backup and recovery.

BEST SQL practice In Oracle Database

sysdate - interval '2' hour;

sysdate - interval '2' day;

sysdate - interval '2' month;

sysdate - interval '2' minute;

sysdate - interval '2' second;

---- to enable and disable history on SQLPLUS

history;

show history;

hist clear;

hist;

set history off;

set history on;

--- fetch or rownum ---

fetch next n rows;

fetch first n rows;

fetch next n rows only;

fetch first n percent rows only;

offset n rows fetch next n rows only;

you can use gv_$view_name instead of gv$view_name

Friday, July 21, 2023

Linux User Group

--Note : Make sure to logged in as "root" user for using usermod,useradd,userdel, groupadd,groupmod,groupdel on Linux otherwise commands will not run.

To pass the password with command to sudo :-

echo "password" | sudo -S -u username linux_command

to run command with another user:- su -c "command" username

echo "k" | sudo -S -u king whoami

--------------------------------------------------------------------------------

## to change home directory/Login directory of a user in Linux

usermod -d /path/to/new_home_directory -m username

option:

-d : for new path home directory

-m : moving all files from old home directory to new home directory

---------------------------------------------------------------------------------------

to add user:- useradd -m name_of_user

usermod -aG sudo username ----------> to assign admin access to user

sudo su - username

userdel -f username -------------------> to delete user forcefully

groupadd group_name --------------> to add group

groupdel -f group_name ------------> to force delete a group

gpasswd -A user -M user,user,user groupname ----> adding users to a group

gpasswd -d username groupname -----> removing a user from a group

groupmod -n newgroup oldgroup  ------> renaming a group

add user to secondary group:- useradd -G group1,group2,group3 username

add user to primary group :- useradd -g group1,group2,group3 username

add exiting user to secondary group :- usermod -a -G group_name username

add existing user to primary group :- usermod -g group_name username

to view passwd:- cat /etc/passwd

to view group:- cat /etc/groups

to view all user details:- id username

to view shells:- cat /etc/shells or echo $SHELL

=========================================

Rename User in Linux

1. Ensure the user is logged out
ps -u <old_username> pkill -u <old_username>

2. Rename the User
usermod -l <new_username> <old_username>
ex. usermod -l doe john

3. Update the Home Directory
usermod -d /home/<new_username> -m <new_username>
usermod -d /home/doe -m doe

4. Rename the Group
groupmod -d <new_groupname> <old_groupname>
groupmod -d doe john

5. verfiy changes
cat /etc/passwd; cat /etc/shadow; cat /etc/group
grep <new_username> /etc/passwd
grep <new_username> /etc/group

=====================================

To Lock the user on Linux:

sudo usermod -L <username>

This locks the account by prefixing the password hash with ! in /etc/shadow.

To Unlock the user on Linux:

sudo usermod -U <username>

===================================

To change shell of a user in Linux:

usermod -s /path/new_shell <username>

ex. usermod -s /bin/zsh pune

verfiy changes in grep -i /etc/passwd "pune"

or

sudo chsk -s /path/new_shell <username>

sudo chsh -s /bin/sh pune

verify changes in grep -i /etc/passwd "pune"

==============================================

Tuesday, July 18, 2023

Oracle dbms_scheduler, enable, disable and drop Jobs Step by Step

1. Executing a stored procedure:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'PROCESS_DATA_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN process_data; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; INTERVAL=1', -- Run daily
enabled => TRUE
);
END;
/

2. Executing Anonymous PL/SQL Block:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'ANONYMOUS_BLOCK_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN /* Your PL/SQL logic here */ NULL; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=HOURLY; INTERVAL=2', -- Run every 2 hours
enabled => TRUE
);
END;
/

3. Running SQL Statements:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'SQL_STATEMENT_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
EXECUTE IMMEDIATE ''DELETE FROM employees WHERE hire_date <TRUNC(SYSDATE) - 365'';
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=WEEKLY; INTERVAL=1', -- Run weekly
enabled => TRUE
);
END;
/

4. Running OS Commands:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'OS_COMMAND_JOB',
job_type => 'EXECUTABLE',
job_action => '/usr/bin/my_script.sh', -- Replace with your actual script path
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; INTERVAL=1', -- Run daily
enabled => TRUE
);
END;
/

-- Create the job

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'MY_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN /* Your PL/SQL logic here */ NULL; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; INTERVAL=1', -- Run daily
enabled => TRUE
);
END;
/

-- Disable the job

BEGIN
DBMS_SCHEDULER.DISABLE('MY_JOB');
END;
/

-- Enable the job again

BEGIN

DBMS_SCHEDULER.ENABLE('MY_JOB');

END;

SELECT job_name, enabled, state

FROM user_scheduler_jobs; -- Use DBA_SCHEDULER_JOBS or ALL_SCHEDULER_JOBS for all jobs in the database

1. Executing a stored procedure:

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

job_name => 'PROCESS_DATA_JOB',

job_type => 'PLSQL_BLOCK',

job_action => 'BEGIN process_data; END;',

start_date => SYSTIMESTAMP,

repeat_interval => 'FREQ=DAILY; INTERVAL=1', -- Run daily

enabled => TRUE

);

END;

2. Executing Anonymous PL/SQL Block:

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

job_name => 'ANONYMOUS_BLOCK_JOB',

job_type => 'PLSQL_BLOCK',

job_action => 'BEGIN /* Your PL/SQL logic here */ NULL; END;',

start_date => SYSTIMESTAMP,

repeat_interval => 'FREQ=HOURLY; INTERVAL=2', -- Run every 2 hours

enabled => TRUE

);

END;

3. Running SQL Statements:

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

job_name => 'SQL_STATEMENT_JOB',

job_type => 'PLSQL_BLOCK',

job_action => 'BEGIN

EXECUTE IMMEDIATE ''DELETE FROM employees WHERE hire_date < TRUNC(SYSDATE) - 365'';

END;',

start_date => SYSTIMESTAMP,

repeat_interval => 'FREQ=WEEKLY; INTERVAL=1', -- Run weekly

enabled => TRUE

);

END;

4. Running OS Commands:

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

job_name => 'OS_COMMAND_JOB',

job_type => 'EXECUTABLE',

job_action => '/usr/bin/my_script.sh', -- Replace with your actual script path

start_date => SYSTIMESTAMP,

repeat_interval => 'FREQ=DAILY; INTERVAL=1', -- Run daily

enabled => TRUE

);

END;

-- Create the job

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

job_name => 'MY_JOB',

job_type => 'PLSQL_BLOCK',

job_action => 'BEGIN /* Your PL/SQL logic here */ NULL; END;',

start_date => SYSTIMESTAMP,

repeat_interval => 'FREQ=DAILY; INTERVAL=1', -- Run daily

enabled => TRUE

);

END;

5. Run job secondly

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'SQL_STATEMENT_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
EXECUTE IMMEDIATE ''DELETE FROM employees WHERE hire_date <TRUNC(SYSDATE) - 365'';
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=SECONDLY; INTERVAL=1', -- Run weekly
enabled => TRUE
);
END;
/

6. Run job minutely

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'SQL_STATEMENT_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
EXECUTE IMMEDIATE ''DELETE FROM employees WHERE hire_date <TRUNC(SYSDATE) - 365'';
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MINUTELY ; INTERVAL=1', -- Run weekly
enabled => TRUE
);
END;
/

-- Disable the job

BEGIN

DBMS_SCHEDULER.DISABLE('&job_name');

END;

-- Enable the job again

BEGIN

DBMS_SCHEDULER.ENABLE('&job_name');

END;

--- Drop a Job

BEGIN

DBMS_SCHEDULER.DROP_JOB(

job_name => '&job_name',

force => FALSE,

defer => FALSE

);

END;

SELECT job_name, enabled, state

FROM dba_scheduler_jobs where job_name='&job_name'; -- Use DBA_SCHEDULER_JOBS or ALL_SCHEDULER_JOBS for all jobs in the database

select * from all_scheduler_running_jobs;

select job,what,broken from dba_jobs;

select job_name,owner,enabled from dba_scheduler_jobs;

disable job:-

execute dbms_scheduler.disable('owner.job');

enable job:-

execute dbms_scheduler.enable('owner.job');

drop job:-

execute dbms_scheduler.drop_job('owner.job');

grant revoke delete truncate update drop statement

grant select on schema.table to &user;

revoke select on schema.table from &user;

delete schema.table where column='&column_name';

truncate table schema.table;

update schema.table set &column_name=&value where &column_name=&value;

Pluggable databases (PDBS)

select * from v$pdbs;

alter pluggable database all open;

alter pluggable database pdb_name save state;

alter pluggable database all save state ;

SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO

ALTER PLUGGABLE DATABASE pdb$seed,pdb OPEN READ ONLY FORCE;
ALTER PLUGGABLE DATABASE pdb$seed,pdb CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE ALL OPEN;
ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE ALL EXCEPT pdb$seed OPEN;
ALTER PLUGGABLE DATABASE ALL EXCEPT pdb CLOSE IMMEDIATE;

startup force;

startup open read write;

startup open read only;

startup upgrade;

shutdown immediate;

shutdown abort;

Oracle DB Patching Steps

Hint:- first shutdown database and listener

1. copy patch zip and latest opatch utility to desired directory

2. unzip patch zip file

3. cp opatch_zip file to $ORACLE_HOME/

4. cd $ORACLE_HOME/

5. mv OPatch OPatch_old

6. unzip opatch_zip

7. you will see new OPatch directory in $ORACLE_HOME/

8. cd OPatch/

9. run ./opatch version

10. cd to patch_zip directory

11. cd 32435354/

12. 32435354~]$ $ORACLE_HOME/OPatch/opatch apply

13. if unwanted happens then rollback changes using below

32435354~]$ $ORACLE_HOME/OPatch/opatch rollback -id 32435354

14. In the Final step run datapatch utility after starting database and listener

$ORACLE_HOME/OPatch/datapatch -verbose

if possible open all pdbs (pluggable databases)

select * from v$pdbs;

alter pluggable database all open;

alter pluggable database pdb_name save state;

alter pluggable database all save state ;

GRID PATCHING :-

cd patch_directory then execute below:-

make sure you've exported all oracle environment variables ORACLE_HOME,ORACLE_SID,PATH,

LD_LIBRARY_PATH,etc.

$ORACLE_HOME/OPatch/opatchauto apply -oh $ORACLE_HOME

$ORACLE_HOME/OPatch/opatchauto apply /path/to/patch_directory -oh $ORACLE_HOME

Monday, July 17, 2023

UNLOCK STATS ON TABLES, INDEXES, PARTITION, MATERIALIZED VIEW, SCHEMA

Unlock tables stats

BEGIN

DBMS_STATS.UNLOCK_TABLE_STATS(ownname => 'schema_name', tabname => 'table_name');

END;

Unlock Index Stats

BEGIN

DBMS_STATS.UNLOCK_INDEX_STATS(ownname => 'schema_name', indname => 'index_name');

END;

Unlock Partition Stats

BEGIN

DBMS_STATS.UNLOCK_PARTITION_STATS(ownname => 'schema_name', tabname => 'table_name', partname => 'partition_name');

END;

Unlock Materialized View Stats

BEGIN

DBMS_STATS.UNLOCK_MVIEW_STATS(ownname => 'schema_name', mvname => 'materialized_view_name');

END;

Unlock Schema Stats

BEGIN

DBMS_STATS.UNLOCK_SCHEMA_STATS(ownname => 'schema_name');

END;

EXPDP and IMPDP In Oracle Database

Best Practice Examples For EXPDP and IMPDP Jobs:-

By default everything in database is exported and imported you can choose for your needs parameters.

select * from all_directories where directory_name='&directory';

-----------------------------------------------------------------------------

How to use export import utility from linux terminal :-

$ expdp user_who_started_job/password attach=job_name

How to export / import using "sysdba"

$ expdp \"/ as sysdba\"

$ impdp \"/ as sysdba\"

$ impdp user_who_started_job/password attach=job_name
export > status
import > status
export > exit
import > exit

import / export > help

Note:-

Default behaviour of impdp:-

1. by default imports all objects

2. impdp customize using 'include' and 'exclude' if you don't specify include/exclude then

impdp will imports all objects from the dmp files.

** all objects :- tables, indexes, views, triggers, procedures, functions, packages, constraints, types, etc.**

3.'impdp also imports others related data, such as constraints, grants and synonyms.

4. target db already contains objects with the same name as the imported objects, default behaviour is

to overwrite the existing objects with the imported objects. you can change behaviour using remap_table,

remap_schema, remap_

when to use remap_* :-

schema consolidation, renaming objects, data migration, filtering data

remap_data