Oracle Database Scripts

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