LOGS :----->CRS, RDBMS, PDBS, ASM LOGS LOCATION ?

 That's all you need:

# Alert Logs

tail -n 1000 ${ORACLE_BASE}/diag/rdbms/<dbname>/<instance_name>/trace/alert_<instance_name>.log

# Listener Logs

tail -n 1000 ${ORACLE_BASE}/diag/tnslsnr/$(hostname)/listener/trace/listener.log

# Clusterware Logs

tail -n 1000 ${GRID_HOME}/log/$(hostname)/alert$(hostname).log

=> CRS: 

$ORACLE_BASE/diag/crs/$(hostname)/crs/trace/alert.log

=> ASM: 

$ORACLE_BASE/diag/+asm/<ASM_SID>/trace/alert_<ASM_SID>.log

=> RDBMS-Instances: $ORACLE_BASE/rdbms/<DB_NAME>/<DB_SID>/trace/alert_<DB_SID>.log

=> PDBs: There is not specific log for PDBS. Please see the alert.log of the corresponding RDBMS instances

 Locations of Oracle Clusterware Component Log Files

best :- ${GRID_HOME}/log/$(hostname)

Component	Log File LocationFoot 1
Cluster Ready Services Daemon (CRSD) Log Files	Grid_home/log/host_name/crsd
Cluster Synchronization Services (CSS)	Grid_home/log/host_name/cssd
Cluster Time Synchronization Service (CTSS)	Grid_home/log/host_name/ctssd
Grid Plug and Play	Grid_home/log/host_name/gpnpd
Multicast Domain Name Service Daemon (MDNSD)	Grid_home/log/host_name/mdnsd
Oracle Cluster Registry	Oracle Cluster Registry tools (OCRDUMP, OCRCHECK, OCRCONFIG) record log information in the following location: Grid_home/log/host_name/client Cluster Ready Services records Oracle Cluster Registry log information in the following location: Grid_home/log/host_name/crsd
Oracle Grid Naming Service (GNS)	Grid_home/log/host_name/gnsd
Oracle High Availability Services Daemon (OHASD)	Grid_home/log/host_name/ohasd
Event Manager (EVM) information generated by evmd	Grid_home/log/host_name/evmd
Oracle RAC RACG	The Oracle RAC high availability trace files are located in the following two locations: Grid_home/log/host_name/racg $ORACLE_HOME/log/host_name/racg Core files are in subdirectories of the log directory. Each RACG executable has a subdirectory assigned exclusively for that executable. The name of the RACG executable subdirectory is the same as the name of the executable. Additionally, you can find logging information for the VIP and database in these two locations, respectively.
Server Manager (SRVM)	Grid_home/log/host_name/srvm
Disk Monitor Daemon (diskmon)	Grid_home/log/host_name/diskmon
Grid Interprocess Communication Daemon (GIPCD)	Grid_home/log/host_name/gipcd

Listener error :- listener know no service

[oracle@pm ~]$ cat /u02/app/oracle/homes/OraDB21Home1/network/admin/listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pm)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )



ADR_BASE_LISTENER = /u02/app/oracle

 oracle@pm ~]$ lsnrctl status

LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 22-JAN-2024 23:09:56
Copyright (c) 1991, 2021, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=pm)(PORT=1522)))
STATUS of the LISTENER
------------------------

Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 21.0.0.0.0 - Production
Start Date                22-JAN-2024 23:02:59
Uptime                    0 days 0 hr. 6 min. 56 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u02/app/oracle/homes/OraDB21Home1/network/admin/listener.ora
Listener Log File         /u02/app/oracle/diag/tnslsnr/pm/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pm.shaikh.com)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))

listener currently know no service...

follow below steps:-

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

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pm.shaikh.com)(PORT=1522)))';

SQL> alter system register;

SQL> startup force;

$ lsnrctl reload 

$ lsnrctl status

netca 

ipc protocol key = extproc

tcp as it is 

How to comment tables in oracle database ?

# How to comment oracle database #

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

COMMENT ON TABLE your_table_name
IS 'This is a multi-line comment for your table.
It provides additional information about the table structure and purpose.';

SELECT table_name, comments

FROM all_tab_comments

WHERE table_name = 'your_table_name';

Server patching Database patching on Production server

* Server patching or upgrades * 

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

apps down 
database down 
sa reboot server / patching/upgrades
database up
apps up

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

 

* Database patching/upgrades Server patching/upgrades *

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

apps down
gg down
database down
sa reboot server/ patching/upgrades
database up
apps up 
gg up 

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

EXPDP & IMPDP

 How to find failed tables after export/import job has done on Oracle Database ?

cat expdp.log | grep -i "error" | grep -o '\"[[:graph:]]\{0,255\}\".\"[[:graph:]]\{0,255\}\"' > failed_tables.txt


it'll show failed tables having formatted in the expdp.log file as below :-
"schema"."table_name"

Hi Team, Please use these steps for schema refresh when export is perform by normal exp/imp utlity. Step 1:- Take a export using expdp of user that needs to refresh. Example :- Expdp system/xyz directory=DPMUP dumpfile=dmp_file_name_%U.DMP LOGFILE=dmp_file_name_IMP_EXP.LOG SCHEMAS=user1,user2 Step 2:- drop the user.( make sure app is down ). Step 3:- import the metadata only from dumpfile ( created in step1 ). Example :- impdp system/xyz directory=DPMUP dumpfile=dmp_file_name_%U.DMP LOGFILE=dmp_file_name_IMP_EXP.LOG REMAP_SCHEMA=source_schema:target_schema,source_schema:target_schema CONTENT=METADATA_ONLY EXCLUDE=TABLE Note:- exclude table else you will get error like this during data import. ORA-02291: integrity constraint (owner.FK79B62C533C17239) violated - parent key not found Or you need to disable all constraints manually before data import. Step 4:- crosscheck for any missing role from source schema. SELECT * FROM dba_role_privs where GRANTEE in(‘owner'); --- on source. GRANTEE GRANTED_ROLE ADM DEF ------------------------------ ------------------------------ --- --- owner APPLICATION_DEVELOPER NO YES SQL >SELECT * FROM dba_role_privs where GRANTEE='owner_schema'; --- on target. GRANTEE GRANTED_ROLE ADM DEF ------------------------------ ------------------------------ --- --- owner1 APPLICATION_CREATOR NO YES owner1 NEWT_SYSPRIV NO YES this is the missing role ( APPLICATION_DEVELOPER ) on target. SQL >grant APPLICATION_DEVELOPER to schema; Step 5:- Check for the tablespace used by source schema. SQL >select distinct tablespace_name from dba_segments where owner='schema'; ---- On target. TABLESPACE_NAME ------------------------------ ts1

SQL>select distinct tablespace_name from dba_segments where owner='schema'; ----- On source. TABLESPACE_NAME ------------------------------ t1

t2 Target schema don’t have quota on users in this case. SQL >alter user schema quota unlimited on users; User altered. Step 6:- This is the last step ,now we are good to start import. imp system/cowboy01 file=dmp_file_%U.dmp fromuser=source_user touser=target_user log=dmp_file_name.log ignore=y STATISTICS=NONE

USERID=system/your_password

DIRECTORY=DATA_PUMP_DIR

DUMPFILE=exp_objects.dmp

LOGFILE=exp_objects.log

SCHEMAS=HR

INCLUDE=TABLE,INDEX,VIEW,SEQUENCE,CONSTRAINT,SYNONYM,ROLE_GRANT,SYSTEM_GRANT,

OBJECT_GRANT

GOLDEN GATE 19c Installation On Linux

Enable supplemental logging for a specific table:-

ALTER TABLE your_schema.your_table ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Enable supplemental logging :-

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Enable supplemental logging for a column in a table:-

ALTER TABLE your_schema.your_table 

  ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, COLUMN1, COLUMN2, ...);

create goldengate user :-

# useradd -m goldengate

--add goldengate user to oracle groups --

# sudo usermod -aG $(groups oracleuser | cut -d' ' -f3- | sed 's/ /,/g') goldengate

# sudo usermod -g oinstall goldengate 

-- add following info in /home/goldengate/.bash_profile

export ORACLE_HOME=/u02/app/ogg19c

export LD_LIBRARY_PATH=$ORACLE_HOME:$ORACLE_HOME/lib

export PATH=$ORACLE_HOME:$PATH

su - oracle
GG_HOME=/u02/app/oracle/GG_HOME
cd GG_HOME
wget https://download.oracle.com/otn_software/linux/instantclient/1921000/instantclient-basic-linux.x64-19.21.0.0.0dbru.zip
chmod 777 *.zip
unzip *.zip 
cd instant_client_19_21
mv * ../ 
./ggsci 

su - oracle
sqlplus / as sysdba
create user ggadmin identified by ggadmin;
alter user ggadmin quota unlimited on ggadmin;
grant create session, connect, resource, alter system, select any dictionary, flashback any table to ggadmin container=all;
exec dbms_goldengate_auth.grant_admin_privilege(grantee => 'ggadmin',container=>'all');
PL/SQL procedure successfully completed.
alter user ggadmin set container_data=all container=current;
grant alter any table to ggadmin container=ALL;
alter system set enable_goldengate_replication=true scope=bot
alter database force logging;
alter pluggable database add supplemental log data;
Pluggable database altered.
-- to add integrated extract 
su - goldengate
cd $GG_HOME
./ggsci
add extract ext, integrated tranlog, begin now
info all
add exttrail /u02/app/oracle/GG_HOME/lt, extract ext
--start extract always from cdb$root 
start extract ext atscn <scn>;
[goldengate@pm ~]$ cat /home/goldengate/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs
export ORACLE_HOME=/u02/app/oracle/product/19c/dbhome_1
export ORACLE_SID=sakri
export GG_HOME=/u02/app/oracle/GG_HOME
export PATH=$GG_HOME:$PATH
export LD_LIBRARY_PATH=$GG_HOME/lib:$LD_LIBRARY_PATH
[goldengate@pm ~]$
[goldengate@pm ~]$
[goldengate@pm ~]$
[goldengate@pm ~]$
[goldengate@pm ~]$ cat /u02/app/oracle/GG_HOME/dirprm/ext.prm
EXTRACT ext
setenv (ORACLE_SID = "sakri")
setenv (ORACLE_HOME = "/u02/app/oracle/product/19c/dbhome_1")
USERID ggadmin, PASSWORD ggadmin
EXTTRAIL /u02/app/oracle/GG_HOME/dirprm/lt
--TRANLOGOPTIONS ASMUSER your_asm_user, ASMPASSWORD your_asm_password
TABLE cdb$root.ggadmin.*;

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;

GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 51> delete extract ext
Deleted EXTRACT EXT.

GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 52> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING

GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 53> edit param ext


GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 54> add extract ext, integrated tranlog, begin now
EXTRACT (Integrated) added.

GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 55> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     STOPPED     EXT         00:00:00      00:00:01
GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 96> add exttrail /u02/app/oracle/GG_HOME/dirprm/lt, extract ext
EXTTRAIL added.
GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 97> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     ABENDED     EXT         00:00:00      00:01:18

GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 98> start extract ext
Sending START request to MANAGER ...
EXTRACT EXT starting

GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 56> register extract ext database
2024-03-01 23:36:09  ERROR   OGG-08223  ERROR: One or more containers must be specified when registering Extract for a container database.

GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 57> register extract ext database container (pdb)
2024-03-01 23:36:58  INFO    OGG-02003  Extract EXT successfully registered with database at SCN 2267932.

GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 58> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     STOPPED     EXT         00:00:00      00:01:05

GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 59> start extract ext
Sending START request to MANAGER ...
EXTRACT EXT starting

GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 60> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     STOPPED     EXT         00:00:00      00:01:10

GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 61> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     STOPPED     EXT         00:00:00      00:01:12

GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 62> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     STOPPED     EXT         00:00:00      00:01:13

GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 63> register extract ext database container all
2024-03-01 23:37:23  ERROR   OGG-15406  Missing opening parenthesis in container list for REGISTER EXTRACT command.

GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 64> register extract ext database container (pdb$seed)
2024-03-01 23:37:53  ERROR   OGG-01754  Cannot register or unregister EXTRACT EXT because the Extract is currently running. Stop the Extract and retry the command.

GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 65> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EXT         00:00:00      00:02:01

GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 66> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EXT         00:00:00      00:02:02

GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 67> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EXT         00:00:00      00:02:04

GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 68> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EXT         00:00:00      00:02:05

GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 69> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EXT         00:00:00      00:02:07

GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 70> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EXT         00:00:00      00:02:09

GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 71> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EXT         00:00:00      00:02:12

GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 72> info ext
ERROR: You must specify a group name.
GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 73> info extract ext
EXTRACT    EXT       Initialized   2024-03-01 23:35   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:02:18 ago)
Process ID           8654
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2024-03-01 23:35:57
                     SCN 0.0 (0)

GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 74> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EXT         00:00:00      00:02:20

GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 75> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EXT         00:00:00      00:02:22

GGSCI (pm.shaikh.com as ggadmin@sakri/CDB$ROOT) 76>

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