ORACLE RAC 10G-11G COMMANDs

 crs_stat -t  :- show HA resource status

crsstat :-  output of crs_stat -t formatted nicely 

ps -ef | grep d.bin :- crsd.bin evmd.bin ocssd.bin

crsctl check crs :- css, crs, evm appears healthy.

crsctl stop crs :- stop crs and all other services.

crsctl disable crs* :- prevents crs from starting on reboot.

crsctl enable crs* :- enables crs start on reboot.

crs_stop -all :- stops all regiestered resources.

crs_start -all :- starts all registered resources. 

srvctl start instance -d  <db_name> -i <inst_name> :-  starts an instance.

srvctl start database -d <db_name> :-  starts all instances. 

srvctl stop database -d <db_name> :- stops all instances. 

srvctl stop instance -d <db_name> -i <inst_name> :- stops an instance.

srvctl start service -d <db_name> -s <service_name> :- starts a service.

srvctl stop  service -d <db_name> -s <service_name> :- stops a service.

srvctl status service -d <db_name> :- check status of a service.

srvctl status instance -d <db_name> -i <inst_name> :-  checks status of instance

srvctl status database -d <db_name> :- checks status of all instances.

srvctl start nodeapps -n <node_name> :- starts gsd, vip, listener and ons

srvctl stop nodeapps -n <node_name> :- stops gsd, vip, listener and ons 

oracle environment variables

cat /etc/profile

cat ~/.profile 

cat ~/.bash_profile

cat /etc/oraInst.loc inventory_loc=/u01/app/oraInventory
inst_group=oinstall

Details about all /etc/files

 The "/etc" directory in Linux contains a wide range of configuration files and directories that are essential for the proper functioning of the system and various software applications. Below are some common files and directories found in "/etc" and their typical usage:
/etc/passwd: This file contains user account information, including usernames, user IDs, group IDs, home directories, and shell types.

/etc/group: It stores information about user groups, including group names and group IDs.

/etc/shadow: This file holds encrypted password information for user accounts.

/etc/hostname: It stores the system's hostname, which is used to identify the machine on a network.

/etc/hosts: This file maps hostnames to IP addresses, allowing the system to resolve hostnames to IP addresses without using DNS.

/etc/hostname: It specifies the system's hostname, which is used during system boot.

/etc/resolv.conf: Contains DNS resolver configuration, including the IP addresses of DNS servers.

/etc/network/interfaces (or /etc/sysconfig/network-scripts): These files are used to configure network 

interfaces on the system.

/etc/fstab: It lists the file systems and partitions to be mounted at boot and their associated options.

/etc/mtab: This file contains a list of currently mounted file systems.

/etc/ssh/sshd_config: Configuration file for the OpenSSH server.

/etc/sudoers: Configuration for the sudo command, which allows users to execute commands as superusers.

/etc/apt/sources.list (Debian/Ubuntu) or /etc/yum.conf (RHEL/CentOS): Configuration files for package management systems. They define where the package manager should look for software packages.

/etc/crontab: The system-wide crontab file for scheduling tasks at specific times or intervals.

/etc/sysctl.conf: Configuration file for the sysctl command, which allows you to configure kernel parameters.

/etc/hostnamectl (systemd-based systems): This file contains system hostname and related information.

/etc/selinux/config (SELinux-enabled systems): Configuration file for SELinux, a mandatory access control system.

*/etc/default (or /etc/default/ on Debian-based systems)**: These directories store default configuration settings for various services.

/etc/X11/xorg.conf (or /etc/X11/xorg.conf.d/ on some systems): Configuration files for the X Window System.

/etc/samba/smb.conf (if Samba is installed): Configuration file for the Samba file-sharing service.

Please note that the above list is not exhaustive, and the files and directories in the "/etc" directory can vary between different Linux distributions and systems. Each file serves a specific purpose in configuring and managing the system and its software components. It's essential to use caution when making changes to these files, as misconfiguration can affect system stability and security.

archivelogs location in oracle

 select inst_id,name,value from v$diag_info;

best for finding exact location of archivelog location in oracle linux:-

select dest_name,status,destination from gv$archive_dest where status='VALID';

SELECT name, space_limit, space_used FROM v$recovery_file_dest;

DBMS PACKAGES IN ORACLE

 

rm -rf !(folder) in linux

shopt -s extglob  # Enable extended pattern matching

rm -rf !(folder)  # Delete everything except the 'folder' directory

shopt -u extglob  # Disable extended pattern matching (optional)

savepoint, rollback, commit in oracle db

 commit: make changes done in transaction permanent.

rollback : rollback the state of database to the last commit point.

savepoint : use to specify a point in transaction to which later you can rollback.

create table student 

(

id number primary key,

name varchar2(50),

college varchar2(50)

);

insert all

into student values (1,'pravin','ness')

into student values (2,'prakash','ness')

into student values (3,'pratap','ness')

into student values (4,'pranav','ness')

into student values (5,'prayank','ness')

select * from dual;

--create savepoint student_original --

savepoint student_original;

-- delete some records --

delete student where id=5;

-- rollback to student_original savepoint --

rollback to student_original;

-- most important fact --

you cannot rollback to any previous savepoint once you've triggered commit; 

Linux SSH REMOTE EXECUTION OF COMMANDS

 ssh -t -q oracle@pm "(df -h /u02; hostname -f; date)" 

read -p "enter server name: " server ; ssh -t -q $server "(sudo -iu oracle)"

or looping multiple servers :-

for servers in server1 server2 server3 server4 ;

do 

ssh -t -q $servers "(df -h /u02; hostname -f ; date)" ;

done;

EXPLANATION:- 

• ssh: This is the SSH command used for secure remote shell access.

• -t: This option is used to allocate a pseudo-terminal. It's often necessary when you want to run interactive commands remotely because it simulates an interactive terminal session.

• -q: This option stands for "quiet" and is used to suppress warning and diagnostic messages. It makes the SSH command less verbose.

• oracle@pm: This part specifies the username (oracle) and the hostname or IP address (pm) of the remote server you want to connect to. The @ symbol separates the username from the hostname.

• "(...)": The parentheses enclose a series of commands that you want to execute on the remote server. In this case, three commands are enclosed: df -h /u02, hostname -f, and date.

• -t option is essential when running multiple commands, as it ensures that the commands are executed in an environment that behaves like an interactive shell session.

File System Alerts Short Cuts Commands specially "find command"

 

[oracle@pm u02]$ find ./* -type d -name "archivelog" -exec du -sh {} +  2>&1 | grep -v "Permission denied"

612M    ./app/oracle/fast_recovery_area/CHENNAI/archivelog
660M    ./app/oracle/fast_recovery_area/SAKRI/archivelog

[oracle@pm u02]$ find ./* -type d -name "trace" -exec du -sh {} +  2>&1 | grep -v "Permission denied"

0       ./app/oracle/product/21c/dbhome_1/network/trace

17M     ./app/oracle/diag/rdbms/chennai/chennai/trace

2.9M    ./app/oracle/diag/rdbms/sakri/chennai/trace

29M     ./app/oracle/diag/rdbms/sakri/sakri/trace

40K     ./app/oracle/diag/rdbms/unknown/chennai/trace

4.0K    ./app/oracle/diag/clients/user_oracle/host_3163451292_110/trace

1.3M    ./app/oracle/diag/tnslsnr/pm/listener/trace

0       ./app/oracle/homes/OraDB21Home1/network/trace

[oracle@pm u02]$ find ./* -type d -name "alert" -exec du -sh {} +  2>&1 | grep -v "Permission denied"

8.9M    ./app/oracle/diag/rdbms/chennai/chennai/alert

168K    ./app/oracle/diag/rdbms/sakri/chennai/alert

11M     ./app/oracle/diag/rdbms/sakri/sakri/alert

16K     ./app/oracle/diag/rdbms/unknown/chennai/alert

20K     ./app/oracle/diag/clients/user_oracle/host_3163451292_110/alert

2.2M    ./app/oracle/diag/tnslsnr/pm/listener/alert

[oracle@pm u02]$ find ./* -type d -name "*backup*" -exec du -sh {} +  2>&1 | grep -v "Permission denied"

4.6M    ./app/oracle/product/21c/dbhome_1/inventory/backup

164M    ./app/oracle/fast_recovery_area/CHENNAI/autobackup

19M     ./app/oracle/fast_recovery_area/SAKRI/autobackup

12K     ./app/oraInventory/backup

[oracle@pm u02]$ find ./* -type d -name "*dump*" -exec du -sh {} +  2>&1 | grep -v "Permission denied"

158M    ./app/oracle/product/21c/dbhome_1/rdbms/utl/upgrade_dump_seed

0       ./app/oracle/diag/rdbms/chennai/chennai/cdump

0       ./app/oracle/diag/rdbms/sakri/chennai/cdump

0       ./app/oracle/diag/rdbms/sakri/sakri/cdump

0       ./app/oracle/diag/rdbms/unknown/chennai/cdump

0       ./app/oracle/diag/clients/user_oracle/host_3163451292_110/cdump

0       ./app/oracle/diag/tnslsnr/pm/listener/cdump

18M     ./app/oracle/admin/chennai/adump

4.0K    ./app/oracle/admin/chennai/dpdump

13M     ./app/oracle/admin/sakri/adump

[oracle@pm u02]$ find ./* -type d -name "*crs*" -exec du -sh {} +  2>&1 | grep -v "Permission denied"

208M    ./app/oracle/product/21c/dbhome_1/crs

24K     ./app/oracle/product/21c/dbhome_1/inventory/Components21/oracle.rdbms.crs

32K     ./app/oracle/product/21c/dbhome_1/inventory/Templates/crs

0       ./app/oracle/diag/crs

others RAC GRID:-

find /opt -type d -name "*crsd*" 2>&1 | grep -v "Permission denied"

find ./* -type d -name "*crsd*"  2>&1 | grep -v "Permission denied"

find ./* -type d -name "*evm*"  2>&1 | grep -v "Permission denied"

find ./* -type d -name "*orarootagent*"   2>&1 | grep -v "Permission denied"

find ./* -type d -name "*oraagent*"   2>&1 | grep -v "Permission denied"

find ./* -type d -name "*ohas*"    2>&1 | grep -v "Permission denied"

+++++++++++ To find files older than some hours +++++++++++++++++++++++++++++++

find . -daystart -type f -newermt "$(date -d 'today 10 hours ago' +'%Y-%m-%d %H:%M:%S')"

find . -type f -writable

find . -type f -executable

find . -type f -readable 

+++++++++++++files older between 5 hours to 10 hours from the daystart++++++++++++++

find .  -daystart -type f -newermt "$(date -d 'today 10 hours ago' +'%Y-%m-%d %H:%M:%S')" ! -newermt "$(date -d 'today 5 hours ago' +'%Y-%m-%d %H:%M:%S')"

find . -type f -newermt '5 hours ago' 

find . -type f -newermt '5 minutes ago' 

find . -type f -newermt '5 days ago' 

find . -type f -newermt '5 months ago' 

find . -type f -newermt '5 years ago' 

find . -type f -newermt 'yesterday' 

we can use 

"-type d" flag for directories 

"-type b" flag for block devices

"-type f" flag for files 

NLS (NATIONAL LANGUAGE SUPPORT) in SQL

 NLS 

(national language support)

nls_sort and nls_comp are parameters used to 
control the sorting and comparison behaviour of
characters in SQL queries. 

1. NLS_SORT:-
binary-binary sorting (ascii)
french-french linguistic sorting.
german-german linguistic sorting.
spanish-spanish linguistic sorting
binary_ci-binary sorting with case-insensitivity.
french_ci-french linguistic sorting with case-insensitivity.
example:-
alter session set nls_sort='FRENCH';

2. NLS_COMP:-
binary-binary comparison (case-sensitive).
linguistic- linguistic comparison(case-insensitive).
example:-
alter session set nls_comp='LINGUISTIC';

overall best for case-insensitive search in SQL oracle:-

alter session set nls_sort='binary_ci';

alter session set nls_comp='linguistic';

select * from nls_database_parameters;

select * from nls_instance_parameters;

select * from nls_session_parameters;

PL/SQL TRICKS SINGLE AND DOUBLE AMPERSAND AND BIND VARIABLE

 How to use below:-

&value, &column_name 👉for temporary submission of variable every time you execute a query it'll ask you for entering the value.

&&variable or &&column_name:-

iit's use to set permanent submission in current session;

BIND VARIABLES (:VARIABLE):-

GROUP BY EXPRESSION:-

select column1,column2,column3,...,columnN from table_name

group by column1,column2,column3,...,columnN;

we've to mention all columns in group by expression as below highlighted text...