Installation of Oracle Software 11203 in Linux and basic database adminstration

Hai, in this article I will demonstrate how to installation of ORACLE SOFTWARE and DATABASE of version 11.2.0.3 x64 Bit.

Linux installed on VM-Ware Linux version 5.7_x64 Bit

List of Topics (Linux, Database, RAC, EBS)

I have provided the links for complete installations in following Links:

1. Openfiler Installation and configuration, 
2. Linux Installtion
3. Grid Installation
4. Database Installation
5. Database Quick Installation Guide (12.1- ORACLE DOC)

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

Download PuTTY: latest release

putty.exe (the SSH and Telnet client itself)

Download link Putty 64bit       Download link for putty 32 bit

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

Assumptions:

Machine	IP Address	DB Name	SID	Listener
RAC1	192.168.1.11	DELL	DELL	DELL

Certification Information for Oracle Database on Linux x86-64 (Doc ID 1304727.2)

Oracle Database Documentation  >> Installing and Upgrading >> Linux Installation Guides

login as: root

root@192.168.1.11's password:

Last login: Thu Mar  9 07:47:38 2017 from 192.168.1.1

[root@rac1 ~]# hostname

rac1.dell.com

[root@rac1 ~]# hostname -i

192.168.1.11

[root@rac1 ~]# cat /etc/enterprise-release

Enterprise Linux Enterprise Linux Server release 5.7 (Carthage)

[root@rac1 ~]#

[root@rac1 ~]# arch

x86_64
[root@rac1 ~]# who -r
         run-level 5  2018-01-30 07:57                   last=S

[root@rac1 ~]#

[root@rac1 ~]# cat /etc/hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1               localhost.localdomain localhost

::1             localhost6.localdomain6 localhost6
##-- Public-IP
192.168.1.11    rac1.dell.com   rac1
192.168.1.12    rac2.dell.com   rac2
192.168.1.13    rac3.dell.com   rac3
##-- Private-IP
10.0.0.11 rac1-priv.dell.com rac1-priv
10.0.0.12 rac2-priv.dell.com rac2-priv
10.0.0.13 rac3-priv.dell.com rac3-priv
##-- Virtual-IP
192.168.1.21 rac1-vip.dell.com rac1-vip
192.168.1.22 rac2-vip.dell.com rac2-vip
192.168.1.23 rac3-vip.dell.com rac3-vip
##-- SCAN IP
192.168.1.30 dellc-scan.dell.com dellc-scan
192.168.1.31 dellc-scan.dell.com dellc-scan
192.168.1.32 dellc-scan.dell.com dellc-scan
##-- Storage-IP
192.168.1.40    san.dell.com    san

[root@rac1 ~]#
[root@rac1 ~]# service network restart
Shutting down interface eth0:                              [  OK  ]
Shutting down interface eth1:                              [  OK  ]
Shutting down loopback interface:                          [  OK  ]
Bringing up loopback interface:                            [  OK  ]
Bringing up interface eth0:                                [  OK  ]
Bringing up interface eth1:                                [  OK  ]

[root@rac1 ~]# 

Additional Requirements for Installation

Recommended Software Packages for Linux5.7

https://docs.oracle.com/cd/E11882_01/install.112/e24326/toc.htm#BHCGJCEA

The following or later version of packages for Oracle Linux 5, Red Hat Enterprise Linux 5, and Asianux Server 3 must be installed:

How I Simplified Oracle Database 12c and 11g Installations on Oracle Linux 6: 

--FOR Linux 7,6:

# yum search oracle-rdbms-server-12cR*

# yum -y install oracle-rdbms-server-11gR2-preinstall

# yum -y install oracle-rdbms-server-12cR1-preinstall

# yum -y install oracle-database-server-12cR2-preinstall

--FOR Linux 5:

# yum -y install oracle-validated

--FOR EBS R12.2 along with additional RPM's

# yum -y install oracle-ebs-server-R12-preinstall

# yum -y install openmotif21

# yum -y install xorg-x11-libs-compat

binutils-2.17.50.0.6

compat-libstdc++-33-3.2.3

compat-libstdc++-33-3.2.3 (32 bit)

elfutils-libelf-0.125

elfutils-libelf-devel-0.125

gcc-4.1.2

gcc-c++-4.1.2

glibc-2.5-24

glibc-2.5-24 (32 bit)

glibc-common-2.5

glibc-devel-2.5

glibc-devel-2.5 (32 bit)

glibc-headers-2.5

ksh-20060214

libaio-0.3.106

libaio-0.3.106 (32 bit)

libaio-devel-0.3.106

libaio-devel-0.3.106 (32 bit)

libgcc-4.1.2

libgcc-4.1.2 (32 bit)

libstdc++-4.1.2

libstdc++-4.1.2 (32 bit)

libstdc++-devel-4.1.2

make-3.81

sysstat-7.0.2

Linux Packages Installation without dependencies:
[root@rac1 R122rpms]#
[root@rac1 R122rpms]# rpm -ivh openmotif21-2.1.30-11.EL5.i386.rpm --nodeps --force
Preparing...                ########################################### [100%]
   1:openmotif21            ########################################### [100%]

[root@rac1 R122rpms]#

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

Kernel Parameters

Using any text editor, create or edit the “/etc/sysctl.conf” file, and add or edit lines similar to the following:

fs.aio-max-nr = 1048576

fs.file-max = 6815744

kernel.shmall = 2097152

kernel.shmmax = 536870912

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

net.ipv4.ip_local_port_range = 9000 65500

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

Run the following command to load in SYSCTL settings from the file specified or /etc/sysctl.conf

/sbin/sysctl –p

[root@rac1 ~]# sysctl -p

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

Resource Limits

Add the following lines to the "/etc/security/limits.conf" file

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

oracle soft stack 10240

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

Create Central Inventory location and Group

# more /etc/oraInst.loc

inventory_loc=/u01/app/oraInventory

inst_group=oinstall

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

Disable secure linux by editing the "/etc/selinux/config" file, making sure the SELINUX flag is set as follows
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#       enforcing - SELinux security policy is enforced.
#       permissive - SELinux prints warnings instead of enforcing.
#       disabled - SELinux is fully disabled.
SELINUX=disabled
# SELINUXTYPE= type of policy in use. Possible values are:
#       targeted - Only targeted network daemons are protected.
#       strict - Full SELinux protection.
SELINUXTYPE=targeted

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

Prepare for ORACLE RDMS installation

[root@rac1 ~]# xhost +

access control disabled, clients can connect from any host

[root@rac1 ~]#

[root@rac1 ~]# cd /mnt/hgfs/D/ORACLE_SOFTWARES/DB11203_x64/V28748-01

[root@rac1 V28748-01]# ll

total 3398321

-rwxrwxrwx 1 root root 1358454646 Oct 12  2011 p10404530_112030_Linux-x86-64_1of7.zip

-rwxrwxrwx 1 root root 1142195302 Oct 12  2011 p10404530_112030_Linux-x86-64_2of7.zip

Oracle Software is in Windows Machine I Have Shared my D-Drive with Linux.

Create a folder in Linux and Unzip files to the folder.

Change permission levels for the database folder

[root@rac1 V28748-01]# mkdir /shrf/db11203_x64

[root@rac1 V28748-01]# chown -R oracle:oinstall /shrf/

[root@rac1 V28748-01]# su oracle

[oracle@rac1 V28748-01]$ pwd

/mnt/hgfs/D/ORACLE_SOFTWARES/DB11203_x64/V28748-01

[oracle@rac1 V28748-01]$ unzip p10404530_112030_Linux-x86-64_1of7.zip -d /shrf/db11203_x64

[oracle@rac1 V28748-01]$ unzip p10404530_112030_Linux-x86-64_2of7.zip -d /shrf/db11203_x64

[oracle@rac1 V28748-01]$ cd /shrf/db11203_x64/database

[oracle@rac1 database]$ ll

total 64

drwxr-xr-x 12 oracle oinstall  4096 Sep 19  2011 doc

drwxr-xr-x  4 oracle oinstall  4096 Sep 22  2011 install

-rwxr-xr-x  1 oracle oinstall 28122 Sep 22  2011 readme.html

drwxr-xr-x  2 oracle oinstall  4096 Sep 22  2011 response

drwxr-xr-x  2 oracle oinstall  4096 Sep 22  2011 rpm

-rwxr-xr-x  1 oracle oinstall  3226 Sep 22  2011 runInstaller

drwxr-xr-x  2 oracle oinstall  4096 Sep 22  2011 sshsetup

drwxr-xr-x 14 oracle oinstall  4096 Sep 22  2011 stage

-rwxr-xr-x  1 oracle oinstall  5466 Aug 23  2011 welcome.html

[oracle@rac1 database]$

Check the display parameters

[oracle@rac1 database]$ xclock

[oracle@rac1 database]$ echo $DISPLAY

[oracle@rac1 database]$ export DISPLAY=:0.0

If you got any rpm missing in prerequisite check then install it

Run the following Scripts with ROOT user

[root@rac1 ~]# /u01/app/oraInventory/orainstRoot.sh

Changing permissions of /u01/app/oraInventory.

Adding read,write permissions for group.

Removing read,write,execute permissions for world.

Changing groupname of /u01/app/oraInventory to oinstall.

The execution of the script is complete.

[root@rac1 ~]#

[root@rac1 ~]#

[root@rac1 ~]# /u01/app/oracle/product/11.2.0/dbhome_1/root.sh

Performing root user operation for Oracle 11g

The following environment variables are set as:

    ORACLE_OWNER= oracle

    ORACLE_HOME=  /u01/app/oracle/product/11.2.0/dbhome_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:

   Copying dbhome to /usr/local/bin ...

   Copying oraenv to /usr/local/bin ...

   Copying coraenv to /usr/local/bin ...

Creating /etc/oratab file...

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root script.

Now product-specific root actions will be performed.

Finished product-specific root actions.

[root@rac1 ~]#

Now Press OK in the Window

Check the installer log file for any errors

[oracle@rac1 database]$ ./runInstaller

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 27374 MB    Passed

Checking swap space: must be greater than 150 MB.   Actual 29996 MB    Passed

Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-03-09_08-13-08AM. Please wait ...[oracle@rac1 database]$ You can find the log of this install session at:

 /u01/app/oraInventory/logs/installActions2017-03-09_08-13-08AM.log

[oracle@rac1 database]$ cat /u01/app/oraInventory/ContentsXML/inventory.xml

<HOME NAME="OraDB19Home1" LOC="/u01/app/oracle/product/19.0.0.0/db_1" TYPE="O" IDX="2"/>

[oracle@rac1 database]$

Installation of DATABASE  DELL

Before Insatllaing create required Directories.

[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/DELL/arch/

[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/flash_recovery_area/

[oracle@rac1 ~]$

Create a environment file for Database installation or later

[oracle@rac1 ~]$ vi ~/dell.env

i#export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH

export TNS_ADMIN=$ORACLE_HOME/network/admin

export EXPDP=$ORACLE_BASE/expdp

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib/usr/lib; export LD_LIBRARY_PATH

#export OMS_HOME=/u01/app/oracle/middleware/

#export AGENT_HOME=/u01/app/oracle/agent/agent_inst/

export ORACLE_UNQNAME=DELL

export ORACLE_SID=DELL

Create Database DELL

[oracle@rac1 ~]$ . dell.env

[oracle@rac1 ~]$ .env | grep ORA

ORACLE_SID=DELL

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

[oracle@rac1 ~]$ dbca

Provide Password asper your requirement

Database has created successfully

Connect to DELL database

Specially for OCI Database:

To check list of RDBMS homes(only) on servers from root:

OCI db home CPU patches methods  Parts Need to check and verify

Use the cliadm update-dbcli command to update the database CLI with the latest new and updated commands.

OCI DB Patching a DB System in OCI Using dbcli

Oracle Database CLI Reference

List failed jobs in OCI Console for PSU patching/upgrade

[root@rac1 ~]#   dbcli list-jobs|tail -50

[root@rac1 ~]#   dbcli describe-job -i <failed job id for xxx> -l Verbose

[root@rac1 ~]#   ps -ef | grep -i dcs

[root@rac1 ~]#   df -h |grep -i opt

[root@rac1 ~]#   ls -ltr /opt/oracle/dcs/bin/dbcli

[root@rac1 ~]#   tailf /opt/oracle/dcs/log/dcs-agent.0.0.log

[root@rac1 ~]#   /opt/zookeeper/bin/zkServer.sh status

[root@rac1 ~]#   dbcli describe-system -d

[root@rac1 ~]#   dbcli describe-component

[root@rac1 ~]#   dbcli describe-latestpatch

[root@rac1 ~]#   dbcli describe-database -in AHCCDEV

[root@rac1 ~]#   dbcli describe-dbhome -i e371e412-a85b-4aa2-9d1f-6e1e7b91f17a

[root@rac1 ~]#   dbcli describe-netsecurity -H e371e412-a85b-4aa2-9d1f-6e1e7b91f17a

[root@rac1 ~]#   dbcli describe-dbstorages -i 0eedd6ee-9485-45da-be70-aa38f07e95f0

[root@rac1 ~]#   dbcli describe-objectstoreswifts -i 

[root@rac1 ~]#   dbcli describe-schedules

[root@rac1 ~]#   dbcli describe-job -i d6ee-9485-45da....

[root@rac1 ~]#   dbcli describe-job -i d6ee-9485-45da.... -l Verbose

[root@rac1 ~]#   dbcli list-databases

[root@rac1 ~]#   dbcli list-dbhomes

[root@rac1 ~]#   dbcli list-nodes

[root@rac1 ~]#   dbcli list-dbstorages

[root@rac1 ~]#   dbcli list-dgconfigs

[root@rac1 ~]#   dbcli list-logSpaceUsage

[root@rac1 ~]#   dbcli list-objectstoreswifts

[root@rac1 ~]#   dbcli list-schedules

[root@rac1 ~]#   dbcli list-jobs

  

[root@rac1 ~]#   dbcli describe-component

System Version

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

21.4.2.0.0

Component                                Installed Version    Available Version

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

GI                                        19.10.0.0.0           19.13.0.0

DB                                        12.1.0.2.210119       12.1.0.2.211019

[root@rac1 ~]#   dbcli update-dbhome -i e371e412-a85b-4aa2-9d1f-6e1e7b91f17a

OCI encrypted TDE wallet password & sys password verification if any doubt:

ls -ltrh $ORACLE_HOME/dbs/orapw*

ls -ltrh /opt/oracle/dcs/commonstore/wallets/tde/*

SQL> select * from v$pwfile_users;

SQL> select WRL_PARAMETER from v$encryption_wallet;

cd /opt/oracle/dcs/commonstore/wallets/tde/*

mkstore -wrl . -list >>>>>>>>>>>>>>> when prompted for password, pls input source DB TDE password. If it is correct, encryption keys are listed.

Oracle Secret Store Tool : Version 12.1.0.2

Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:XX_10_DigiT_1XXX#

Oracle Secret Store entries:

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

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

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

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

How to check whether a database is on Exadata or not?

select * from dba_feature_usage_statistics where name in ('Exadata','Hybrid Columnar Compression');

select * from (select count( distinct cell_name) from v$cell_state);

select * from gv$cell_state;

select count(*) from (select distinct cell_name from gv$cell_state);

select case when count(cell_name) > 0 then 'EXADATA'

else 'NOT EXADATA' END "IsExadata"

from v$cell_state;

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

download latest opatch

latest opatch   6880880

[oracle@rac1 ~]$   cat /etc/oratab

DELL:/u01/app/oracle/product/19.0.0.0:N

[oracle@rac1 ~]$ 

[oracle@rac1 ~]$  $ORACLE_HOME/OPatch/opatch version

Oracle Interim Patch Installer version 1.0.0.0.64

Copyright (c) 2011 Oracle Corporation. All Rights Reserved..

Oracle recommends you to use the latest OPatch version

and read the OPatch documentation available in the OPatch/docs

directory for usage. For information about the latest OPatch and

other support-related issues, refer to document ID 293369.1

available on My Oracle Support (https://myoraclesupport.oracle.com)

OPatch Version: 1.0.0.0.64

To Check all applied patches to Oracle HOMES ( RDBMS/GRID )

$ORACLE_HOME/OPatch/opatch lspatches

$ORACLE_HOME/OPatch/opatch lsinventory | grep applied

$ORACLE_HOME/OPatch/opatch lsinventory | grep description

$ORACLE_HOME/OPatch/opatch lsinventory > Analyze_with_OPatch_Online.txt

[oracle@rac1 ~]$    $ORACLE_HOME/OPatch/opatch version | grep "Installer version"

Oracle Interim Patch Installer version 1.0.0.0.64

[oracle@rac1 ~]$

Check Patch Conflict methods with current patches applied in HOME:

1. Manual Command to check patch Conflict is go to patch folder and run:

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

2. Run patch conflict check online, with option "Analyze with opatch" prior downloading patch (useful if having Big patch, check conf. without downloading patch)

$ORACLE_HOME/OPatch/opatch lsinventory > Analyze_with_OPatch_Online.txt

- click Analyze with opatch (near Download Button) 

- Attach/upload the above txt file to the patch with which need to check conflicts

- click "Analyze for Conflict"

[oracle@rac1 ~]$ $ORACLE_HOME/OPatch/opatch rollback -id 18485835

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

List of Background process for Database DELL:
[oracle@rac1 ~]$
[oracle@rac1 ~]$ ps -ef | grep -i DELL|wc -l
36
[oracle@rac1 ~]$
[oracle@rac1 ~]$ ps -ef | grep -i DELL
oracle   30100     1  0 13:47 ?        00:00:00 ora_pmon_DELL
oracle   30102     1  0 13:47 ?        00:00:00 ora_psp0_DELL
oracle   30104     1  1 13:47 ?        00:00:05 ora_vktm_DELL
oracle   30108     1  0 13:47 ?        00:00:00 ora_gen0_DELL
oracle   30110     1  0 13:47 ?        00:00:00 ora_mman_DELL
oracle   30114     1  0 13:47 ?        00:00:00 ora_diag_DELL
oracle   30116     1  0 13:47 ?        00:00:00 ora_dbrm_DELL
oracle   30118     1  0 13:47 ?        00:00:00 ora_vkrm_DELL
oracle   30120     1  0 13:47 ?        00:00:00 ora_dia0_DELL
oracle   30122     1  0 13:47 ?        00:00:00 ora_dbw0_DELL
oracle   30124     1  0 13:47 ?        00:00:00 ora_lgwr_DELL
oracle   30126     1  0 13:47 ?        00:00:00 ora_ckpt_DELL
oracle   30128     1  0 13:47 ?        00:00:00 ora_smon_DELL
oracle   30130     1  0 13:47 ?        00:00:00 ora_reco_DELL
oracle   30132     1  0 13:47 ?        00:00:00 ora_lreg_DELL
oracle   30134     1  0 13:47 ?        00:00:00 ora_pxmn_DELL
oracle   30136     1  0 13:47 ?        00:00:01 ora_mmon_DELL
oracle   30138     1  0 13:47 ?        00:00:00 ora_mmnl_DELL
oracle   30140     1  0 13:47 ?        00:00:00 ora_d000_DELL
oracle   30142     1  0 13:47 ?        00:00:00 ora_s000_DELL
oracle   30155     1  0 13:47 ?        00:00:00 ora_tmon_DELL
oracle   30157     1  0 13:47 ?        00:00:00 ora_tt00_DELL
oracle   30159     1  0 13:47 ?        00:00:00 ora_smco_DELL
oracle   30166     1  0 13:47 ?        00:00:00 ora_w000_DELL
oracle   30169     1  0 13:47 ?        00:00:00 ora_w001_DELL
oracle   30172     1  0 13:47 ?        00:00:00 ora_aqpc_DELL
oracle   30174     1  0 13:47 ?        00:00:00 ora_cjq0_DELL
oracle   30178     1  0 13:47 ?        00:00:00 ora_p000_DELL
oracle   30180     1  0 13:47 ?        00:00:00 ora_p001_DELL
oracle   30182     1  0 13:47 ?        00:00:00 ora_p002_DELL
oracle   30184     1  0 13:47 ?        00:00:00 ora_p003_DELL
oracle   30349     1  0 13:47 ?        00:00:00 ora_qm02_DELL
oracle   30353     1  0 13:47 ?        00:00:00 ora_q002_DELL
oracle   30355     1  0 13:47 ?        00:00:00 ora_q003_DELL
oracle   30357     1  0 13:47 ?        00:00:00 ora_q004_DELL
oracle   30493  3364  0 13:53 pts/0    00:00:00 grep -i DELL
[oracle@rac1 ~]$ 

kills Only Remote DB Connection (do not execute from root insted, use with OS user)

ERROR: ORA-00020: maximum number of processes (200) exceeded

[oramgr@rac1 ~]$  ps -ef | grep LOCAL=NO | awk '{print $2}' | xargs kill -9

ORACLE RDBMS Version and Platform:
[oracle@rac1 ~]$
[oracle@rac1 ~]$ cat /etc/oratab   (path in Linux) 

DELL:/u01/app/oracle/product/11.2.0/dbhome_1:N              # line added by Agent

if it changed to Y then can stop/start db and listener as below: ref Link

DELL:/u01/app/oracle/product/11.2.0/dbhome_1:Y              # line added by Agent

[oracle@rac1 ~]$  $ORACLE_HOME/bin/dbstart $ORACLE_HOME

[oracle@rac1 ~]$  $ORACLE_HOME/bin/dbshut $ORACLE_HOME

root@ebsprodapp3:~# cat /etc/var/opt/oracle/oratab (path in Solaris) 

root@ebsprodapp3:~# prtconf (Solaris RAM Check)

root@ebsprodapp3:~# pkg info entire  (Solaris OS  Infoo)

if Basic DB informantion applicatible in 11g,12C,19c Database;

set pagesize 1000 linesize 200 

col ACTION_TIME for a30;

col COMMENTS for a50

col HOST_NAME for a27;

col COMP_ID for a9

col COMP_NAME for a35;

col SCHEMA for a10;

col STATUS format A15

col VERSION format A12

col MACHINE for a30;

col VALUE for a50

col DESCRIPTION for a100

SPOOL DB_DD_CHECK.TXT

/* Database identification */

select NAME, PLATFORM_ID, DATABASE_ROLE from v$database;

select * from V$version where banner like 'Oracle Database%';

select INSTANCE_NAME, to_char(STARTUP_TIME,'DD/MM/YYYY HH24:MI:SS') "STARTUP_TIME" from gv$instance;

/* Database Component Registry status of below 19c */

select comp_id, comp_name, schema,status, version from dba_registry;

/* Database Component Registry status for 19c */

select comp_id,comp_name,schema,version,VERSION_FULL,status from dba_registry;

@?/rdbms/admin/utlusts.sql

/* Level of patch in the database */

set pagesize 1000 linesize 200 

select * from dba_registry_history;

select PATCH_ID,ACTION,STATUS,ACTION_TIME,DESCRIPTION from dba_registry_sqlpatch;

/* INVALID objects count, by type & in detail */

select count(*) "INVALID Objects Count" from dba_objects where status !='VALID';

/* My Custom Query for Database Status 11g,12c,19c */

select dbid,NAME,INSTANCE_NAME,OPEN_MODE,CREATED,LOG_MODE,FLASHBACK_ON,CONTROLFILE_TYPE,DATABASE_ROLE,GUARD_STATUS,PROTECTION_MODE from gv$database,gv$instance;

/* My Custom Query for Database Status 19c */

select INSTANCE_NUMBER,INSTANCE_NAME,DATABASE_TYPE,HOST_NAME,STATUS,DATABASE_STATUS,INSTANCE_ROLE,ACTIVE_STATE,INSTANCE_MODE from gv$instance;

/* Parameter file details */

show parameter spfile;

show parameter recovery

show parameter undo_retention

/* ARCHIVE LOG Status */

archive log list;

/* No. of RAC-DB Nodes and connected sessions to Database*/

select inst_id, count(*) from gv$session group by inst_id;

select inst_id, machine, count(*) from gv$session group by inst_id,machine order by machine;

/* Alert Log file of Database for RAC/NON-RAC */

select INST_ID,value from gv$diag_info where name='Diag Trace';

SPOOL OFF

[oracle@rac1 ~]$ . dell.env

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 9 11:42:24 2017

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$version;

SQL> select * from database_properties;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for 64-bit Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL>  

select * from gv$sessions_count; -- to check 19C RAC DB Sessions count

select inst_id, count(*) from gv$session group by inst_id; -- to check 19C RAC DB Sessions count

select inst_id, machine, count(*) from gv$session 

--where machine='apps_server_host'--and status='ACTIVE'

group by inst_id,machine order by machine

SQL> 

set linesize 200 pagesize 400;

col HOST_NAME for a27;

select dbid,NAME,INSTANCE_NAME,OPEN_MODE,CREATED,LOG_MODE,FLASHBACK_ON,CONTROLFILE_TYPE,DATABASE_ROLE,GUARD_STATUS,PROTECTION_MODE from gv$database,gv$instance;

select INSTANCE_NUMBER,INSTANCE_NAME,DATABASE_TYPE,HOST_NAME,STATUS,DATABASE_STATUS,INSTANCE_ROLE,ACTIVE_STATE,INSTANCE_MODE from gv$instance;

show parameter spfile;

show parameter recovery

archive log list;

col ACTION_TIME for a35;

col COMP_ID for a15;

col COMP_NAME for a50;

col SCHEMA for a10;

col VERSION for a15;

col VERSION_FULL for a15;

col ACTION_TIME for a35;

select comp_id,comp_name,schema,version,status from dba_registry;

select comp_id,comp_name,version,version_full,status from dba_registry;

select PATCH_ID,ACTION,STATUS,ACTION_TIME,DESCRIPTION from dba_registry_sqlpatch;

select count(*) "INVALID Objects Count" from dba_objects where status !='VALID';

select count(*) from tab;

      DBID NAME      INSTANCE_NAME    OPEN_MODE            CREATED   LOG_MODE     CONTROL FLASHBACK_ON       DATABASE_ROLE    GUARD_S PROTECTION_MODE

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

4092997409 DELL      DELL1           READ WRITE           31-MAR-23 ARCHIVELOG   CURRENT NO                 PRIMARY          NONE    MAXIMUM PERFORMANCE

SQL> 

INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME STATUS       DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST INSTANCE_MO

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

              2 CDELL2           rac2      OPEN         ACTIVE            PRIMARY_INSTANCE   NORMAL    REGULAR

              1 CDELL1           rac1      OPEN         ACTIVE            PRIMARY_INSTANCE   NORMAL    REGULAR

SQL>

SQL>

NAME                                 TYPE        VALUE

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

spfile                               string      +DATA/DELL/PARAMETERFILE/spfi

                                                 le.333.1132960993

SQL>

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      +RECO

db_recovery_file_dest_size           big integer 999G

recovery_parallelism                 integer     0

remote_recovery_file_dest            string

SQL>

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

SQL>

set linesize 200 pagesize 400;

col HOST_NAME for a35;

select DBID,DB_NAME,INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME from dba_hist_database_instance;--list all history of instance 

Recovery File Dest for flashback

SQL> select * FROM v$recovery_area_usage

SQL> select * FROM v$recovery_file_dest;

show parameter sessions

show parameter processes

show parameter sga

show parameter pga

show parameter recovery

show parameter log_archive_dest

To check component list and last applied bundle patch:

set linesize 200 pagesize 400;

col COMP_ID for a15;

col COMP_NAME for a50;

col SCHEMA for a10;

col VERSION for a15;

col VERSION_FULL for a15;

col ACTION_TIME for a35;

select comp_id,comp_name,schema,version,status from dba_registry;

select comp_id,comp_name,version,version_full,status from dba_registry;

select PATCH_ID,ACTION,STATUS,ACTION_TIME,DESCRIPTION from dba_registry_sqlpatch;

COMP_ID         COMP_NAME                                          VERSION                        STATUS

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

MGD             Oracle Machine Generated Data                      12.1.0.2.0                     VALID

XDB             Oracle XML Database                                12.1.0.2.0                     VALID

CONTEXT         Oracle Text                                        12.1.0.2.0                     VALID

SDO             Spatial                                            12.1.0.2.0                     VALID

ORDIM           Oracle Multimedia                                  12.1.0.2.0                     VALID

CATALOG         Oracle Database Catalog Views                      12.1.0.2.0                     VALID

CATPROC         Oracle Database Packages and Types                 12.1.0.2.0                     VALID

JAVAVM          JServer JAVA Virtual Machine                       12.1.0.2.0                     VALID

CATJAVA         Oracle Database Java Packages                      12.1.0.2.0                     VALID

XML             Oracle XDK                                         12.1.0.2.0                     VALID

RAC             Oracle Real Application Clusters                   12.1.0.2.0                     OPTION OFF

APS             OLAP Analytic Workspace                            12.1.0.2.0                     VALID

XOQ             Oracle OLAP API                                    12.1.0.2.0                     VALID

13 rows selected.

SQL> 

SQL> 

set linesize 220 pagesize 200;

col ACTION_TIME for a35;

select PATCH_ID,ACTION,STATUS,ACTION_TIME,DESCRIPTION from dba_registry_sqlpatch;--last applied BUNDLE PATCH (BP) in EBS DB

SQL> select PATCH_ID,ACTION,STATUS,ACTION_TIME,DESCRIPTION from cdb_registry_sqlpatch;

SQL> select * from sys.registry$sqlpatch;

SQL> 

SQL> 

set linesize 220 pagesize 200;

col ACTION_TIME for a35;

col NAMESPACE for a15;

col VERSION for a15;

col COMMENTS for a40;

SELECT * from dba_registry_history order by ACTION_TIME; -- to check history of performed upgrades on database 

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

SQL>

col PARAMETER for a30;

col VALUE for a20;

SELECT * from v$nls_parameters where parameter like '%CHARACTERSET%';

SQL> SELECT value AS db_charset FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET'; -- Database character set

DB_CHARSET

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

AR8MSWIN1256

SQL>

SQL> SELECT value AS db_ncharset FROM nls_database_parameters WHERE parameter = 'NLS_NCHAR_CHARACTERSET'; -- National character set

DB_NCHARSET

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

AL16UTF16

SQL>

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

SQL> select count (*) from DBA_POLICIES; --5171

SQL> select POLICY_GROUP, POLICY_NAME, OBJECT_NAME, PACKAGE, FUNCTION from DBA_POLICIES; --security policies in DB

SQL> desc v$instance

SQL>
SQL>

SQL> select INSTANCE_NAME, STATUS, DATABASE_STATUS, VERSION from  v$instance;

INSTANCE_NAME    STATUS       DATABASE_STATUS   VERSION

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

DELL             OPEN         ACTIVE            11.2.0.3.0

SQL> desc v$database;

SQL>
SQL>
SQL> col PLATFORM_NAME for a17

select NAME, OPEN_MODE, DATABASE_ROLE, PLATFORM_NAME from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    PLATFORM_NAME
--------- -------------------- ---------------- -----------------
DELL      READ WRITE           PRIMARY          Linux x86 64-bit

SQL>


Alert Log File Location (tail -f):

select INST_ID,value from gv$diag_info where name='Diag Trace';

grep diag $ORACLE_HOME/dbs/initDELL.ora

tailf /data/admin/oracle/diag/rdbms/dell/DELL/trace/alert*.log

to check how many time database restarted

[oracle@rac1 ~]$ grep -A 1 'Starting ORACLE instance' alert_TEST.log 

[oracle@rac1 ~]$ grep -B 1 'Instance shutdown complete' alert_TEST.log  

SQL> select INST_ID,value from gv$diag_info where name='Diag Trace';

COLUMN name FORMAT A25

COLUMN value FORMAT A65

set linesize 200 pagesize 400;

SELECT * FROM gv$diag_info;

VALUE

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

/u01/app/oracle/diag/rdbms/dell/DELL/trace

SQL>
[oracle@rac1 ~]$ find $ORACLE_HOME -name *alert*.log -print

Date and time of Database:

[root@rac1 #]$ watch -n 1 date

[root@rac1 #]$ date -s "25 May 2023 12:16:00"

SQL>

SQL> select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') from dual;                

TO_CHAR(SYSDATE,'DD

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

25-06-2018 08:50:20

SQL> 

C-R-D Files Location:

SQL>

SQL> select name from v$controlfile;

NAME

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

/u01/app/oracle/DELL/control01.ctl

/u01/app/oracle/DELL/control02.ctl

SQL>

SQL>

SQL> 

set linesize 200 pagesize 200

col MEMBER for a50

SELECT * FROM V$LOGFILE;

select GROUP#,THREAD#,SEQUENCE#,bytes/1024/1024,MEMBERS,STATUS from v$log;

    GROUP# STATUS  TYPE    MEMBER                                   IS_

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

         3         ONLINE  /u01/app/oracle/DELL/redo03.log          NO

         2         ONLINE  /u01/app/oracle/DELL/redo02.log          NO

         1         ONLINE  /u01/app/oracle/DELL/redo01.log          NO

SQL>

SQL> 

SQL> 

col TABLESPACE_NAME for a20;

col FILE_NAME for a40;

select TABLESPACE_NAME, FILE_NAME,STATUS from dba_data_files where  TABLESPACE_NAME='APPS_TS_NOLOGGING';

TABLESPACE_NAME      FILE_NAME                                STATUS

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

USERS                /u01/app/oracle/DELL/users01.dbf         AVAILABLE

UNDOTBS1        /u01/app/oracle/DELL/undotbs01.dbf       AVAILABLE

SYSAUX             /u01/app/oracle/DELL/sysaux01.dbf        AVAILABLE

SYSTEM             /u01/app/oracle/DELL/system01.dbf        AVAILABLE

SQL>

SQL> select TABLESPACE_NAME, FILE_NAME, STATUS from DBA_TEMP_FILES;

TABLESPACE_NAME      FILE_NAME                                STATUS

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

TEMP                 /u01/app/oracle/DELL/temp01.dbf          ONLINE

SQL>

SQL>

Datafiles Cont in Database:

SQL> select count(*) from dba_data_files;

SQL> select count(*) from v$datafile;

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

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

Free space in Tablespace:

SQL> select tablespace_name , sum(bytes)/1024/1024/1024 from dba_free_space group by tablespacE_name order by 1; --TS Freespace in GB

SQL> select * FROM dba_temp_free_space

To check Database size:

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

An oracle database consists of data files, redo log files, control files, temporary files. The size of the database actually means the total size of all these files. 

select

( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +

( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +

( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +

( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"

from dual;

Database Size Check Query 2:

select 'DATA_n_INDEX: ' || sum(bytes)/1024/1024/1024 || ' GBytes' DATABASE_SIZE from dba_data_files union

select 'TEMP: ' || sum(bytes)/1024/1024/1024 || ' GBytes' from dba_temp_files union

select 'REDO LOGS: ' || sum(bytes)/1024/1024/1024 || ' GBytes' from v$log union

select 'CONTROLFILE: ' || sum(FILE_SIZE_BLKS*BLOCK_SIZE)/1024/1024 || ' MBytes' from v$controlfile;

TO check Schema Size in GB/MB:

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

select sum(bytes)/1024/1024/1024 as size_in_giga, sum(bytes)/1024/1024 as size_in_mega, owner from dba_segments group by owner;-- for all schemas

select 'Size of Schema: '||sum(bytes)/1024/1024/1024||' GBytes'SCHEMA_SIZE FROM dba_segments WHERE owner='KU'-- for individual schemas

TO check Actual size of Tablespace in Database in GB

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

select SUM (bytes)/1024/1024/1024 AS "Size in GB" FROM dba_data_files;

TO check Space Occupied by Data in the Database in GB 

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

This query to get the actual size occupied by Data in the Database.

select SUM (bytes)/1024/1024/1024 AS "Size in GB" FROM dba_segments;

TO check Control file Size in MB:

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

SELECT sum (block_size * file_size_blks)/1024/1024 "Size in (MB)" FROM v$controlfile;

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

To check free space in GB of Tablespace by percentage wise

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

select a.tablespace_name,

A.Allocated_GB, B.Freespace_GB,round(b.freespace_GB/a.allocated_GB*100) "PCT % Free"

from (select tablespace_name ,sum(bytes)/1024/1024/1024 Allocated_GB from dba_data_files group by tablespace_name) A,

(select tablespace_name,sum(bytes)/1024/1024/1024 Freespace_GB

from dba_free_space  group by tablespace_name) 

where a.tablespace_name=b.tablespace_name(+)

--and b.tablespace_name='UAT'--to check status of individual Tablespace

ORDER BY "PCT % Free";

To check free space in datafiles of specific tablespace:

SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,

((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))used_mb,

NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb

FROM v$datafile df, dba_free_space dfs

WHERE df.file# = dfs.file_id(+)

--and dfs.tablespace_name='UAT'-- to check freespace in specific TS datafiles

GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes

ORDER BY file_name;

TO check utilization of Individual Datafile of tablespace, execute this script

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

SQL> col file_name for a25;

SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,

((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))used_mb,

NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb

FROM v$datafile df, dba_free_space dfs

WHERE df.file# = dfs.file_id(+)

GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes

ORDER BY file_name;

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

select tablespace_name, ROUND(bytes_free,2) free,ROUND(bytes_total-bytes_free,2) used, ROUND(bytes_total,2) total,perused from (select a.tablespace_name, a.bytes_free,b.bytes_total, ROUND((100-(100*a.bytes_free/b.bytes_total)),2)  perused from (Select tablespace_name,sum(bytes)/1024/1024 bytes_free From dba_free_space Group by tablespace_name) a, (Select tablespace_name,sum(bytes)/1024/1024 bytes_total From dba_data_files Group by tablespace_name) b where a.tablespace_name=b.tablespace_name) where perused >= 0 ORDER BY tablespace_name;

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

Creation of User and table in Database:

SQL>

SQL> 
grant connect, resource to u1 identified by u1;

grant connect,resource,dba to areef identified by areef;
conn u1/u1

create table emp (EName varchar(15), ENo number);

insert into emp (EName, ENo) values ('aaa',10);

insert into emp (EName, ENo) values ('bbb',20);

insert into emp (EName, ENo) values ('ccc',30);

commit;
select * from emp;

ENAME                  ENO
--------------- ----------
aaa                     10
bbb                     20
ccc                     30

SQL>
SQL> 

Commit complete.

SELECT count(*) FROM DBA_TAB_PRIVS

SELECT * FROM all_tab_privs;

SELECT * FROM USER_TAB_PRIVS

SELECT * FROM dba_sys_privs;--where grantee='aj_live';

SELECT * FROM USER_SYS_PRIVS;

SELECT * FROM dba_role_privs;--where grantee='aj_live';

SELECT * FROM USER_ROLE_PRIVS;

grant DBA to aj_live;--for 11G schemas to check basic db queries(db size, dba_users)

To create profile with lifetime password for database schema:

SQL> SELECT * FROM dba_profiles -- WHERE profile = 'AJAR' 

SQL> select profile from dba_users where username = 'U1';

SQL> 

SQL> CREATE PROFILE AJAR LIMIT

COMPOSITE_LIMIT UNLIMITED

SESSIONS_PER_USER UNLIMITED

CPU_PER_SESSION UNLIMITED

CPU_PER_CALL UNLIMITED

LOGICAL_READS_PER_SESSION UNLIMITED

LOGICAL_READS_PER_CALL UNLIMITED

CONNECT_TIME UNLIMITED

PRIVATE_SGA UNLIMITED

FAILED_LOGIN_ATTEMPTS UNLIMITED

PASSWORD_LIFE_TIME UNLIMITED

PASSWORD_REUSE_TIME UNLIMITED

PASSWORD_REUSE_MAX UNLIMITED

PASSWORD_VERIFY_FUNCTION NULL

PASSWORD_LOCK_TIME UNLIMITED

PASSWORD_GRACE_TIME UNLIMITED;

SQL> 

SQL> alter user u1 profile ajar;

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

How to give oracle kill own session privilege to Developers for locked objects

Suppose we want to give kill session access to the db user testuser1 to kill its own session

SQL>  show user

USER is "SYS"

SQL> 

CREATE OR REPLACE PROCEDURE sys.kill_session(p_sid NUMBER, p_serial NUMBER)

AS

v_user VARCHAR2(30);

BEGIN

SELECT MAX(username)

INTO v_user

FROM v$session

WHERE sid = p_sid

AND serial# = p_serial;

IF v_user IN ('TESTUSER1') THEN --the list can be extended

EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || p_sid || ',' || p_serial || '''';

ELSIF v_user IS NULL THEN

RAISE_APPLICATION_ERROR(-20001,'Session has Expired or Invalid sid/serial Arguments Passed');

dbms_output.put_line(p_sid || 'Session was killed.'); --need to check

ELSE

RAISE_APPLICATION_ERROR(-20002,'Unauthorized Attempt to Kill a Non User Session has been Blocked.');

END IF;

END kill_session;

/

SQL> grant execute on kill_session to TESTUSER1;

SQL> conn testuser1/pswd

SQL>  show user

USER is "TESTUSER1"

SQL> exec sys.kill_session(&SID,&SERIAL#)

---- query to check Locked Objects

SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal,

b.object_id,substr(b.object_name,1,40) object_name,sysdate, c.process

from v$session a, dba_objects b, v$locked_object c

where a.sid = c.session_id

and b.object_id = c.object_id;

---- verify locked Sessions

select sid,serial#, Username ,module from gv$session  where username='testuser1' and sid=601 and serial#=2331;

---- dba role to schrema

grant DBA to aj_live;

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

User with Readonly access (Select Statement) DB version 12.1.0.2

To modify existing datafile, irrespective whether autoextend is off/on or else increment_by is set to any less value like 100MB

ALTER DATABASE DATAFILE '+DATA/PROD/DATAFILE/apps_ts_tx_data.258.1004798189' AUTOEXTEND ON NEXT 1G MAXSIZE 30G;

CREATE TABLESPACE KALHOUT DATAFILE '+DATA' size 1G  AUTOEXTEND ON NEXT 1G MAXSIZE 30G;

CREATE TABLESPACE XXAHCSMS DATAFILE '+DATA' size 2G extent management local segment space management auto; -- creates datafile with 2 gB of dedicated Space without autoextensible option

CREATE TABLESPACE KALHOUT DATAFILE '+DATA'; -- creates datafile with 10 mb with autoextensible option

ALTER TABLESPACE XXFIN ADD DATAFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 30G; -- tested and verified worked fine (+DATA for ASM)

create user arf1 identified by ar1 quota unlimited on users;

ALTER USER arf1 quota unlimited on USERS; -- for ORA-01950:

create user KALHOUT identified by KALHOUT default tablespace KALHOUT temporary tablespace CARPRO_TEMP quota unlimited on KALHOUT;

GRANT connect,READ ANY TABLE to KALHOUT;

conn kalhout/kalhout

select * from tab;

To check user Privilege

SELECT * FROM DBA_SYS_PRIVS where grantee='KALHOUT'

SELECT * FROM USER_SYS_PRIVS;

SELECT * FROM DBA_ROLE_PRIVS where grantee='KALHOUT'

SELECT * FROM USER_ROLE_PRIVS

SELECT * FROM USER_TAB_PRIVS

SELECT count(*) FROM DBA_TAB_PRIVS

To check DDL of user and Roles: (source)

SELECT DBMS_METADATA.GET_DDL('USER','&USER') from dual;

SELECT DBMS_METADATA.GET_DDL('TABLESPACE','&TABLESPACE_NAME') FROM dual;

select dbms_metadata.get_ddl('DIRECTORY','DATAPUMP') FROM DUAL;-- to Get Code for Create Directory

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&USER') FROM DUAL;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&USER') FROM DUAL;

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&USER') FROM DUAL; --if required backup privileges of schema before drop

select owner,object_type,object_name,edition_name from dba_objects where object_name in ('XXXX');-- to check object type

SELECT DBMS_METADATA.GET_DDL('TABLE','&TABLE_NAME') FROM DUAL;-- to check  table code of any user

select dbms_metadata.get_ddl('VIEW','VIEW_NAME','VIEW_OWNER') FROM DUAL;-- to check  view code of any user

select dbms_metadata.get_ddl('SYNONYM','SYNONYM_NAME','SYNONYM_OWNER') FROM DUAL;-- to check  synonym code of any user

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

Invalid Objects Check and running utlrp.sql

SQL>

SQL> show user

USER is "SYS"

SQL> 

col NVL(owner,'GRAND_TOTAL') for a25;

select NVL(owner,'GRAND_TOTAL'),object_type,count(*) from dba_invalid_objects where status='INVALID' group by grouping sets((),(owner,object_type)); 

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)

----------

         1

SQL>

column owner format A12

column object_type format A13

select owner,object_type,COUNT(*)

from dba_objects

where status='INVALID'

group by owner, object_type;

SQL>

SQL> 

column owner format A11

column object_name format A31

column object_type format A15

column last_ddl_time format A10

spool invalids.lst

select OWNER, OBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIME

from dba_objects where status='INVALID'

order by owner;
SQL> 
SQL> 

SQL> grep UTLRP_ alert_UAT.log

SQL> 

SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_BGN  2018-03-08 14:32:41

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid

DOC>   objects in the database. Recompilation time is proportional to the

DOC>   number of invalid objects in the database, so this command may take

DOC>   a long time to execute on a database with a large number of invalid

DOC>   objects.

DOC>

DOC>   Use the following queries to track recompilation progress:

DOC>

DOC>   1. Query returning the number of invalid objects remaining. This

DOC>      number should decrease with time.

DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

DOC>

DOC>   2. Query returning the number of objects compiled so far. This number

DOC>      should increase with time.

DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

DOC>

DOC>   This script automatically chooses serial or parallel recompilation

DOC>   based on the number of CPUs available (parameter cpu_count) multiplied

DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).

DOC>   On RAC, this number is added across all RAC nodes.

DOC>

DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel

DOC>   recompilation. Jobs are created without instance affinity so that they

DOC>   can migrate across RAC nodes. Use the following queries to verify

DOC>   whether UTL_RECOMP jobs are being created and run correctly:

DOC>

DOC>   1. Query showing jobs created by UTL_RECOMP

DOC>         SELECT job_name FROM dba_scheduler_jobs

DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>

DOC>   2. Query showing UTL_RECOMP jobs that are running

DOC>         SELECT job_name FROM dba_scheduler_running_jobs

DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>#

PL/SQL procedure successfully completed.

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_END  2018-03-08 14:32:44

DOC> The following query reports the number of objects that have compiled

DOC> with errors.

DOC>

DOC> If the number is higher than expected, please examine the error

DOC> messages reported with each object (using SHOW ERRORS) to see if they

DOC> point to system misconfiguration or resource constraints that must be

DOC> fixed before attempting to recompile these objects.

DOC>#

OBJECTS WITH ERRORS

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

                  0

DOC> The following query reports the number of errors caught during

DOC> recompilation. If this number is non-zero, please query the error

DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors

DOC> are due to misconfiguration or resource constraints that must be

DOC> fixed before objects can compile successfully.

DOC>#

ERRORS DURING RECOMPILATION

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

                          0

Function created.

PL/SQL procedure successfully completed.

Function dropped.

PL/SQL procedure successfully completed.

SQL>

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)

----------

         0

SQL>

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

Query to check Object Type (function, Package, Procedure, type, view, table):

 SQL> select owner,object_type,object_name,edition_name from dba_objects where object_name in ('EMPLOYEESUNIONVIEW','XXAS_COM_FLEX_PKG','XXAS_CLOUD_SYNC_PKG')

SQL> select NAME,TYPE,COUNT(*) from user_source u where u.NAME in ('package_name','function') group by u.NAME,u.TYPE;

 Query to check if object exist internally any where after it got droped;

 SQL> set linesize 200 pagesize 400;

col name for a35;

select obj#,dataobj#,owner#,name,status,flags,type# from obj$ where name='EMPLOYEESUNIONVIEW';

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

Related Views:

DBA_TEMP_FILES

DBA_DATA_FILES

DBA_TABLESPACES

DBA_TEMP_FREE_SPACE (Oracle 11g)

V$TEMPFILE

V$TEMP_SPACE_HEADER

V$TEMPORARY_LOBS

V$TEMPSTAT

V$TEMPSEG_USAGE

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

Enabling Archive log for Database using Pfile

senario 1 with Date folder for archivelog :

[oracle@rac1 ~]$ cat initDELL.ora

#*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/DELL/archive' #---> disable to use revocery dest (archives generated as per date folder)

*.log_archive_format='%t_%s_%r.dbf'

db_recovery_file_dest='/u01/app/oracle/oradata' #---> for Flashback log Location & archive log date folders

db_recovery_file_dest_size=999G

[oracle@rac1 ~]$

SQL> archive log list;

Database log mode             Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     267

Current log sequence           269

SQL> 

RMAN> crosscheck archivelog all;

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=738 device type=DISK

validation succeeded for archived log

archived log file name=/u01/app/oracle/oradata/DELL/archivelog/2023_04_14/o1_mf_1_269_l3kbrtp4_.arc RECID=277 STAMP=1134102219

validation succeeded for archived log

archived log file name=/u01/app/oracle/oradata/DELL/archivelog/2023_04_14/o1_mf_1_270_l3kbrw6d_.arc RECID=278 STAMP=1134102220

validation succeeded for archived log

archived log file name=/u01/app/oracle/oradata/DELL/archivelog/2023_04_14/o1_mf_1_271_l3kbs035_.arc RECID=279 STAMP=1134102224

validation succeeded for archived log

archived log file name=/u01/app/oracle/oradata/DELL/archivelog/2023_04_14/o1_mf_1_272_l3kbs03g_.arc RECID=280 STAMP=1134102224

Crosschecked 4 objects

RMAN>

Flash back logs generated

Drop guaranteed restore point BEFORE_IMP

Guaranteed restore point BEFORE_IMP dropped

Deleted Oracle managed file /u01/app/oracle/oradata/DELL/flashback/o1_mf_l3f7697d_.flb

Deleted Oracle managed file /u01/app/oracle/oradata/DELL/flashback/o1_mf_l3f76f44_.flb

Deleted Oracle managed file /u01/app/oracle/oradata/DELL/flashback/o1_mf_l3fbos93_.flb

Deleted Oracle managed file /u01/app/oracle/oradata/DELL/flashback/o1_mf_l3ffl136_.flb

senario 2 with custom folder for archivelog :

[root@rac1 ~]# 

[root@rac1 ~]# mkdir -p /u01/app/oracle/DELL/arch

[root@rac1 ~]# chown –R oracle:oinstall /u01

[root@rac1 ~]# chmod –R 775 /u01

[root@rac1 ~]# su - oracle

[oracle@rac1 ~]$

[oracle@rac1 ~]$ . dell.env

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 9 11:42:24 2017

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

SQL> show parameter spfile;

NAME                                 TYPE        VALUE

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

spfile                               string      /u01/app/oracle/product/11.2.0

                                                 /dbhome_1/dbs/spfileDELL.ora

SQL>

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch

Oldest online log sequence     1

Current log sequence           2

SQL>

SQL> create pfile from spfile;

File created.

SQL> shut

Database closed.

Database dismounted.

ORACLE instance shut down.

[oracle@rac1 ~]$
[oracle@rac1 ~]$ cd $ORACLE_HOME/dbs

[oracle@rac1 dbs]$

[oracle@rac1 dbs]$ vi initDELL.ora

DELL.__db_cache_size=432013312

DELL.__java_pool_size=4194304

DELL.__large_pool_size=4194304

DELL.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

DELL.__pga_aggregate_target=419430400

DELL.__sga_target=629145600

DELL.__shared_io_pool_size=0

DELL.__shared_pool_size=176160768

DELL.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/DELL/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/DELL/control01.ctl','/u01/app/oracle/DELL/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='DELL'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=DELLXDB)'

*.memory_target=1047527424

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

log_archive_dest=/u01/app/oracle/DELL/arch

[oracle@rac1 dbs]$

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

VI Editor Command to replace name from "DELL to CLONE" in all places:

[oracle@rac1 dbs]$

[oracle@rac1 dbs]$ vi initDELL.ora
:%s/DELL/CLONE/g

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

[oracle@rac1 dbs]$ 
[oracle@rac1 dbs]$ mv spfileDELL.ora spfileDELL.ora_bkp

[oracle@rac1 dbs]$

[oracle@rac1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 9 11:42:24 2017

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 

SQL> startup mount

ORACLE instance started.

Total System Global Area 1043886080 bytes

Fixed Size                  2219952 bytes

Variable Size             603979856 bytes

Database Buffers          432013312 bytes

Redo Buffers                5672960 bytes

Database mounted.

SQL>

SQL>

SQL> show parameter spfile;

NAME                                 TYPE        VALUE

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

spfile                               string

SQL>

SQL> alter database archivelog;

Database altered.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/DELL/arch

Oldest online log sequence     1

Next log sequence to archive   2

Current log sequence           2

SQL> alter database open;

Database altered.

SQL>

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

DELL      READ WRITE

SQL>

Enable archive log for Database using SPfile.

[root@rac1 ~]#

[root@rac1 ~]# su - oracle

[oracle@rac1 ~]$ . dell.env

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 16 11:58:35 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

SQL>

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE

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

DELL      READ WRITE

SQL>

SQL> alter system set log_archive_dest='/u01/app/oracle/DELL/arch' scope=spfile;

System altered.

SQL>

SQL> shut

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup mount

ORACLE instance started.

Total System Global Area  993775616 bytes

Fixed Size                  2219472 bytes

Variable Size             578814512 bytes

Database Buffers          406847488 bytes

Redo Buffers                5894144 bytes

Database mounted.

SQL>

SQL> alter database archivelog;

Database altered.

SQL>

SQL> alter database open;

Database altered.

SQL>

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/DELL/arch

Oldest online log sequence     1

Next log sequence to archive   2

Current log sequence           2

SQL> show parameter recovery

SQL> show parameter log_archive_dest

SQL> SELECT * FROM V$RECOVERY_AREA_USAGE; --number of arch files

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

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

orapwd file query from Database:  

[oracle@rac1 ~]$ cd $ORACLE_HOME/dbs 

[oracle@rac1  dbs]$

[oracle@rac1  dbs]$ orapwd file=orapwPTEST password=sys4nwr entries=30 force=y

orapwd file creation in ASM for RAC DB 

[oracle@rac1  dbs]$ orapwd file='+DATA' password=sys dbuniquename=TEST

[oracle@rac1  dbs]$ srvctl config database -d $ORACLE_UNQNAME

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID

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

SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE 0

SQL> grant sysdb to aj_live;

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

EXPDP (Data pump) Directory Recreation and location from Database:

Note: for a pluggable database source pdb env and make expdp/impdp HR_LIVE_SCHEMA@XXPDB

Copy command from OCI -> On-premise Servers

[root@prod1-oci ~]# scp root@192.0.100.44:/home/oracle/expdp/AJ_LIVE_01022022.dmp.tar.gz .

Copy command from On-premise -> OCI Servers

[root@rac1-onprem ~]# scp -i /home/applmgr/ssh-key-20.key opc@172.168.53.10:/u02/2022-02-15-dev-fs1.tar.gz .

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

SQL> SELECT * FROM dba_directories where DIRECTORY_NAME='EXPDP';

SQL> drop directory expdp;

Directory dropped.

SQL> 

SQL> create or replace directory expdp as '/u01/app/oracle/expdp';

Directory created.

SQL> exit

[oracle@rac1  dbs]$ mkdir -p /u01/app/oracle/expdp

[oracle@rac1  dbs]$ chmod -R 777 /u01/app/oracle/expdp/

##-- Check the %completion of EXPDP/IMPDP even for RAC DB from gv$session_longops table

--SQL> alter user "SYSTEM" identified by "59awestridge_1";--- in 19c Database to reset system password

--SELECT * FROM dba_datapump_jobs where state='EXECUTING'; -- to check current expdp name

SELECT OPNAME,SID,SERIAL#,CONTEXT,SOFAR,TOTALWORK,   ROUND(SOFAR/TOTALWORK*100,2) "PERCENTAGE_COMPLETE",

TO_CHAR(B.START_TIME,'YYYY/MM/DD HH24:MI:SS') START_TIME

FROM GV$SESSION_LONGOPS B WHERE OPNAME in

(select d.job_name from gv$session s, gv$process p, dba_datapump_sessions d

where p.addr=s.paddr and s.saddr=d.saddr)

AND  OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

##-- when get below error while export drop all not running jobs i.e.(01-99)

ORA-31634: job already exists

ORA-31664: unable to construct unique job name when defaulted

SELECT 'DROP TABLE '||o.OWNER||'.'||o.OBJECT_NAME||' PURGE;'

FROM dba_objects o, dba_datapump_jobs j

WHERE o.owner=j.owner_name

AND o.object_name=j.job_name

AND j.state='NOT RUNNING';

##-- when get below error while export big database of 1TB modify exp-parameter file 

ORA - 39095 : " dump file space has been exhausted. Unable to allocate 4096 bytes" 

DataPump Export (EXPDP) Error ORA-39095 Dump File Space Has Been Exhausted (Doc ID 559692.1)

Solution: dumpfile=<DUMP_NAME>1_%U.dmp, <DUMP_NAME>2_%U.dmp, <DUMP_NAME>3_%U.dmp

##-- when get below error while exporting EBS 12.2 Database, export fails at APPLSYS.FND_LOBS table as the size of table is 210GB

Import Application Attempts Abort With The Error "ORA-01555: snapshot too old: rollback segment number x with name "x" too small" (Doc ID 2231210.1)

10-MAY-23 21:31:52.493: ORA-31693: Table data object "APPLSYS"."FND_LOBS" failed to load/unload and is being skipped due to error:

ORA-02354: error in exporting/importing data

ORA-01555: snapshot too old: rollback segment number 5 with name "_SYSSMU5_1343043110$" too small

Solution: change undo_retention value fromm 900 to 86400

alter system set undo_retention=86400 scope=spfile;

shut immediate

startup

 27718  11-MAY-23 09:32:51.236: . . exported "APPLSYS"."FND_LOBS"                        210.3 GB  166562 rows in 29175 seconds

Full export/import on RAC DB along with date on dump file:

-- before import create required Tablespaces and expdp directory

ps -ef | grep dw0

time expdp system/manager directory=DATAPUMP dumpfile=CTS_`date +%d-%m-%y`_%U.dmp CLUSTER=Y logfile=CTS_exp_`date +%d-%m-%y`.log full=y compression=all parallel=4 logtime=all

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

FOR Database migration from 11.2.0.4 (OS-RHEL 6.9) to 19.18(OS-OL7.9) with full export/import on RAC 

expdp "'/ as sysdba'"  DIRECTORY=expdp DUMPFILE=4upgrd_db_metics.dmp LOGFILE=exp_4upgrd_db_metics.log FULL=Y METRICS=Y EXCLUDE=STATISTICS

Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Mon Mar 6 20:43:32 2023 elapsed 0 00:07:43

-- import Method-1 for db upgrade with metrics

time impdp system/sys DIRECTORY=expdp DUMPFILE=4upgrd_db_metics.dmp LOGFILE=imp_4upgrd_db_metics.log FULL=Y METRICS=Y logtime=all

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 554 error(s) at Mon Mar 6 21:43:33 2023 elapsed 0 00:13:20

-- import Method-2 for db upgrade with version parameter 11.2.0 when made exported without metrics

time impdp system/sys directory=expdp dumpfile=CTS_05-03-23.dmp full=y logfile=CTS_imp_05-03-23.log version=11.2.0 logtime=all

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 567 error(s) at Wed Feb 1 12:18:57 2023 elapsed 0 00:18:01

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

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

--Schema export/import on RAC DB along with date on dump file:

select sum(bytes)/1024/1024/1024 as size_in_giga, segment_type from dba_segments where owner='&USER_NAME' group by segment_type; -- for selected schemas

select owner,sum(bytes)/1024/1024/1024 as "Size in GB" from dba_segments group by owner; -- for all schemas

--To check DDL of user and roles: (source)

--Compare tablespace current sizes b/w source and target before impdp

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&USER') FROM DUAL; -- system grants

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&USER') FROM DUAL; --if required backup privileges of schema before drop

--How To Validate A DataPump Export (EXPDP) Dump File ? (Doc ID 2113967.1)

impdp system/manager directory=expdp dumpfile=HR_LIVE_`date +%d-%m-%y`_%U.dmp logfile=corruption_check.log sqlfile=corruption_check.sql 

time expdp system/manager schemas=HR_LIVE,HR_LIVE01 directory=expdp dumpfile=HR_LIVE_`date +%d-%m-%y`_%U.dmp logfile=HR_LIVE_exp_`date +%d-%m-%y`.log compression=all logtime=all p-arallel=4

impdp system/manager SCHEMAS=HR_LIVE remap_schema=HR_LIVE:HR_LIVE,HR_LIVE01:HR_LIVE01 remap_tablespace=HR_LIVE:HR_LIVE,HR_LIVE01,HR_LIVE01 directory=expdp dumpfile=HR_LIVE_26062021.dmp CLUSTER=N logfile=HR_LIVE_imp_`date +%d-%m-%y`.log logtime=all

##--CLUSTER=N parameter depends from RAC to RAC. In OCI it is recommended

If users data is in default Tablespace "USERS" then do not create SCHEMA and not use remap schema/tablespace command in import syntax, user automatically will get created

impdp system/manager schemas=HR_LIVE directory=expdp dumpfile=HR_LIVE_15-08-22.dmp logfile=HR_LIVE_imp_`date +%d-%m-%y`.log

sqlplus HR_LIVE/hr_live

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

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

Tables export/import on RAC DB: and size of table

IMPDP with TABLE_EXISTS_ACTION - Why is Dependent Metadata Skipped (ORA-39153) ? (Doc ID 1567360.1)

Note: for a pluggable database source pdb env and make expdp/impdp HR_LIVE_SCHEMA@XXPDB

Note: if got below error while multiple table export of particular schema then allow grant to it

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-39087: directory name EXPDP is invalid

grant read,write on directory expdp to HR_LIVE_SCHEMA;

conn icx/icx

col owner for a10;

select t.owner,t.num_rows,t.blocks,t.last_analyzed from dba_tables t where table_name like 'ICX_SESSIONS';

select bytes from user_segments where segment_name='ICX_SESSIONS';

nohup expdp HR_LIVE_SCHEMA TABLES=imptest1,imptest2,imptest3,imptest4,imptest5 directory=expdp dumpfile=imptest_5tables.dmp logfile=imptest_export_5tables.log compression=all &

##--truncate data in tables where import is planned, so all db_objects of tables remain as it is: 

(syntax TRUNCATE TABLE imptest1)

nohup impdp HR_LIVE_SCHEMA TABLES=imptest1,imptest2,imptest3,imptest4,imptest5 directory=expdp TABLE_EXISTS_ACTION=TRUNCAT-x/REPLAC-x dumpfile=imptest_5tables.dmp logfile=imptest_import_5tables.log &

The parameter TABLE_EXISTS_ACTION tells Import what to do if the table it is trying to create already exists.

The possible values have the following effects:

- SKIP leaves the table as is and moves on to the next object. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.

- APPEND loads rows from the source and leaves existing rows unchanged.

- TRUNCATE deletes existing rows and then loads rows from the source.

- REPLACE drops the existing table and then creates and loads it from the source. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.

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

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

TIMESTAMP_TO_SCN CONVERT

Check CURRENT SCN of the Oracle Database

select CURRENT_SCN from v$database;

Example of TIMESTAMP_TO_SCN & SCN_TO_TIMESTAMP function

select timestamp_to_scn(to_timestamp('22-11-2020 19:40:00','DD-MM-YYYY HH24:MI:SS')) "SCN" from dual;

select scn_to_timestamp(39999823174) from dual;

correct scn number format in database

       SCN

----------

4.4140E+10

SQL> SET NUMWIDTH 20;

SQL> /

                 SCN

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

         44139945161

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

Setting SQL prompt in SQL*Plus Oracle

vi $ORACLE_HOME/sqlplus/admin/glogin.sql

SQL> set time on

12:09:04 SQL>

SQL> set sqlprompt "_user'@'_connect_identifier 'on' _date>"

APPS@UAT on 24-JAN-23>

APPS@UAT on 24-JAN-23> 

set sqlprompt "_user'@'_connect_identifier 'on' _date> "

alter session set nls_date_format='dd-Mon-yyyy hh24:mi:ss';

--alter session set nls_date_format='dd/mm/yyyy hh:mi:sspm';

APPS@UAT on 01/24/2023 12:10:24>

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

GATHER SYS SCHEMA STATISTICS FOR DATABASE:  ( 10 + 5 + 1) mins in total

Mandatory to execute one by one else command will not execute at once

DBMS_STATS.GATHER_SCHEMA_STATS got "'DBMS_STATS.AUTO_SAMPLE_SIZE' must be declared" (Doc ID 2804534.1)

SQL> show user

USER is "SYS"

SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name like 'X$%' order by last_analyzed;--1162

SQL> begin

dbms_stats.gather_schema_stats('SYS',options=>'GATHER STALE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO',cascade => TRUE);

 end;

/

SQL> exec dbms_stats.gather_fixed_objects_stats;

SQL> exec dbms_stats.gather_dictionary_stats;

SQL>

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

AWR, ADDM &ASH, sqlhc Report generation (always try to run from location other than ORACLE_HOME)

SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)

select count(*) from dba_hist_snapshot

select snap_id,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where BEGIN_INTERVAL_TIME > systimestamp -1 order by BEGIN_INTERVAL_TIME desc;

cd /u01/app/oracle

ls -ltrh $ORACLE_HOME/rdbms/admin/awrrpt*

ls -ltrh $ORACLE_HOME/rdbms/admin/addmrpt*

ls -ltrh $ORACLE_HOME/rdbms/admin/ashrpt.sql

sqlplus / as sysdba

SQL> @?/rdbms/admin/awrrpt.sql

SQL> @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/awrrpt.sql

Current Instance

~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance

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

 2558404271 AHCOCI11            1 DELL

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

AWR reports can be generated in the following formats.  Please enter the

name of the format at the prompt.  Default value is 'html'.

'html'          HTML format (default)

'text'          Text format

'active-html'   Includes Performance Hub active report

Enter value for report_type: html

Type Specified:  html

Type Specified:  html

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host

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

* 2558404271        1 DELL         DELL         rac1

Using 2558404271 for database Id

Using          1 for instance number

Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed.  Pressing <return> without

specifying a number lists all completed snapshots.

Enter value for num_days:1

Listing the last day's Completed Snapshots

                                                        Snap

Instance     DB Name        Snap Id    Snap Started    Level

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

DELL         DELL              1029 15 Sep 2021 00:00      1

                               1030 15 Sep 2021 01:00      1

                               1031 15 Sep 2021 02:00      1

                               1032 15 Sep 2021 03:00      1

                               1033 15 Sep 2021 04:00      1

                               1034 15 Sep 2021 05:00      1

                               1035 15 Sep 2021 06:00      1

                               1036 15 Sep 2021 07:00      1

                               1037 15 Sep 2021 08:00      1

                               1038 15 Sep 2021 09:00      1

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 1037

Begin Snapshot Id specified: 1037

Enter value for end_snap: 1038

End   Snapshot Id specified: 1038

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrrpt_1_1037_1038.html.  To use this name,

press <return> to continue, otherwise enter an alternative.

Using the report name awrrpt_1_1037_1038.html

. . . . 

End of Report

</body></html>

Report written to awrrpt_1_1029_1030.html

AWR Report values to check:

SQL ordered by User I/O Wait Time

SQL ordered by Reads

SQL ordered by Physical Reads (UnOptimized)

SQL ordered by Cluster Wait Time

SQL>

SQL> @?/rdbms/admin/addmrpt.sql

SQL> @addmrpt.sql

Current Instance

~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance

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

 2558404271 AHCOCI11            1 AHCOCI11

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host

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

* 2558404271        1 DELL         DELL         rac1 

Using 2558404271 for database Id

Using          1 for instance number

Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed.  Pressing <return> without

specifying a number lists all completed snapshots.

Listing the last 3 days of Completed Snapshots

                                                        Snap

Instance     DB Name        Snap Id    Snap Started    Level

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

AHCOCI11     AHCOCI11          1037 15 Sep 2021 08:00      1

                               1038 15 Sep 2021 09:00      1

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 1037

Begin Snapshot Id specified: 1037

Enter value for end_snap: 1038

End   Snapshot Id specified: 1038

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is addmrpt_1_1037_1038.txt.  To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name: Press Enter

Using the report name addmrpt_1_1037_1038.txt

End of Report

Report written to addmrpt_1_1037_1038.txt

SQL> 

SQL>

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

ASH Report generation and sql_id details as per ash/awr report "Top SQL with Top Events":

select count(*) from DBA_HIST_ACTIVE_SESS_HISTORY where SQL_ID='1ycw8vjbqcrq4'

select count(*) from V$ACTIVE_SESSION_HISTORY where SQL_ID like 'gxzm9s0hs2thh'

select program,module,machine from DBA_HIST_ACTIVE_SESS_HISTORY where SQL_ID='1ycw8vjbqcrq4'

ls -ltrh $ORACLE_HOME/rdbms/admin/ashrpt.sql

-rwxrwxrwx. 1 oracle oinstall 5.2K Apr 10  2012 /home/oracle/dbhome_1/rdbms/admin/ashrpt.sql

SQL> show user;

USER is "SYS"

SQL> @?/rdbms/admin/ashrpt.sql

. . . . . 

. . . . .

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Enter 'html' for an HTML report, or 'text' for plain text

Defaults to 'html'

Enter value for report_type: html

Type Specified:  html

. . . . . 

. . . . .

ASH Samples in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Oldest ASH sample available:  13-Jan-22 03:00:37   [  12176 mins in the past]

Latest ASH sample available:  21-Jan-22 13:56:49   [      0 mins in the past]

Specify the timeframe to generate the ASH report

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter begin time for report:

--    Valid input formats:

--      To specify absolute begin time:

--        [MM/DD[/YY]] HH24:MI[:SS]

--        Examples: 02/23/03 14:30:15

--                  02/23 14:30:15

--                  14:30:15

--                  14:30

--      To specify relative begin time: (start with '-' sign)

--        -[HH24:]MI

--        Examples: -1:15  (SYSDATE - 1 Hr 15 Mins)

--                  -25    (SYSDATE - 25 Mins)

Defaults to -15 mins

Enter value for begin_time: 01/20/22 22:00:00

Report begin time specified: 01/20/22 22:00:00

Enter duration in minutes starting from begin time:

Defaults to SYSDATE - begin_time

Press Enter to analyze till current time

Enter value for duration: 120

Report duration specified:   120

Using 20-Jan-22 22:00:00 as report begin time

Using 21-Jan-22 00:00:00 as report end time

. . . . . 

. . . . . 

End of Report

</body></html>

Report written to ashrpt_1_0121_0000.html

SQL>

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

SQLHC report: 

SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)

unzip sqlhc.zip in folder and run script

SQL> START sqlhc.sql "T" 5vkr9skj0p2wk

SQL> @sqlhc.sql    (approx: 30 mins)

Parameter 1:

Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)

Enter value for 1: T

PL/SQL procedure successfully completed.

Parameter 2:

SQL_ID of the SQL to be analyzed (required)

Enter value for 2: 5vkr9skj0p2wk

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

Instance Caging for CPU allocation:

Configuring and Monitoring Instance Caging (Doc ID 1362445.1)

ODA (Oracle Database Appliance): Howto Configuring and Monitoring Instance Caging (Doc ID 1474290.1)

CPU Resource Usage in Linux and Unix ( Doc ID 466996.1 )

How is CPU_COUNT Determined? ( Doc ID 1448389.1 )

SQL> select value from v$osstat where stat_name = 'NUM_CPUS';

     VALUE

----------

        32

Enable Instance Caging

SQL> alter system set cpu_count = 4;

SQL> alter system set resource_manager_plan = 'default_plan';

Monitor Instance Caging

SQL> select name,instance_caging from v$rsrc_plan;

SQL> select to_char(begin_time, 'HH24:MI') time, sum(avg_running_sessions) avg_running_sessions, sum(avg_waiting_sessions) avg_waiting_sessions from v$rsrcmgrmetric_history group by begin_time order by begin_time;

SQL >

"avg_running_sessions" is the average number of running sessions for this minute. If avg_running_sessions is much smaller than cpu_count, the instance is not fully utilizing its cpu_count allocation. cpu_count could be decreased without affecting performance.

"avg_waiting_sessions" is the average number of sessions waiting to be scheduled for this minute. If avg_waiting_sessions is consistently bigger than 0, the performance of the instance could be improved by increasing cpu_count by this amount.

SQL> select * from V$RSRCMGRMETRIC

SQL> select * from v$rsrcmgrmetric_history

V$RSRCMGRMETRIC_HISTORY displays a history (the last one hour) of resource manager metrics, taken from V$RSRCMGRMETRIC. When a resource plan is set, this history is cleared and restarted. This view provides information about resources consumed and wait times per consumer group.

The columns for V$RSRCMGRMETRIC_HISTORY are the same as those for V$RSRCMGRMETRIC.

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

re-attach ORACLE_HOME to Global inventory:

[root@rac1 ~]$ chown -R oraoci:oinstall /u01/app/oraInventory  ß if required 

[oraoci@rac1 ~]$ cat /var/opt/oracle/oraInst.loc

inventory_loc=/u01/app/oraInventory

inst_group=oinstall

[oraoci@rac1 ~]$ cat /u01/app/oraInventory/ContentsXML/inventory.xml

<?xml version="1.0" standalone="yes" ?>

<!-- Copyright (c) 1999, 2021, Oracle and/or its affiliates.

All rights reserved. -->

<!-- Do not modify the contents of this file by hand. -->

<INVENTORY>

<VERSION_INFO>

   <SAVED_WITH>12.2.0.7.0</SAVED_WITH>

   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>

</VERSION_INFO>

<HOME_LIST>

<HOME NAME="OraDB19Home1" LOC="/u01/app/oraoci/product/19.3.0.0/dbhome_1" TYPE="O" IDX="1"/>

</HOME_LIST>

<COMPOSITEHOME_LIST>

</COMPOSITEHOME_LIST>

</INVENTORY>

oraoci@rac1:~$

[oraoci@rac1 bin]$ $ORACLE_HOME/oui/bin/runInstaller -silent -attachHome -invPtrLoc /var/opt/oracle/oraInst.loc ORACLE_HOME="/u01/app/oraoci/product/19.3.0.0/dbhome_1" ORACLE_HOME_NAME="OraGI19Home1"

[oraoci@rac1 ~]$

[oraoci@rac1 bin]$ $ORACLE_HOME/oui/bin/runInstaller -detachHome /u01/app/oraoci/product/19.3.0.0/dbhome_1 -invPtrLoc /var/opt/oracle/oraInst.loc

[oraoci@rac1 bin]$ 

[oraoci@rac1 ~]$

[oraoci@rac1 ~]$ cd $ORACLE_HOME/oui/bin

[oraoci@rac1 bin]$ ./runInstaller -silent -attachHome -invPtrLoc /var/opt/oracle/oraInst.loc ORACLE_HOME="/u01/app/grid/product/19.3.0.0/dbhome_1" ORACLE_HOME_NAME="OraGI19Home1"

[oraoci@rac1 ~]$ 

[oraoci@rac1 ~]$ cat /u01/app/oraInventory/ContentsXML/inventory.xml

<?xml version="1.0" standalone="yes" ?>

<!-- Copyright (c) 1999, 2021, Oracle and/or its affiliates.

All rights reserved. -->

<!-- Do not modify the contents of this file by hand. -->

<INVENTORY>

<VERSION_INFO>

   <SAVED_WITH>12.2.0.7.0</SAVED_WITH>

   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>

</VERSION_INFO>

<HOME_LIST>

<HOME NAME="OraDB19Home1" LOC="/u01/app/oraoci/product/19.3.0.0/dbhome_1" TYPE="O" IDX="1"/>

<HOME NAME="OraGI19Home1" LOC="/u01/app/grid/product/19.3.0.0/dbhome_1" TYPE="O" IDX="2"/>

</HOME_LIST>

<COMPOSITEHOME_LIST>

</COMPOSITEHOME_LIST>

</INVENTORY>

oraoci@rac1:~$

[oraoci@rac1 ~]$ 

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

Suggested Errors and Recommendations:

Unable to open database with Error ORA-017078, ORA-01261:

[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 9 11:42:24 2017

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORA-01078: failure in processing system parameters

LRM-00123: invalid character 128 found in the input file

SQL> exit

SQL> startup
ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated
ORA-01262: Stat failed on a file destination directory

Linux-x86_64 Error: 2: No such file or directory

Please Check and Correct Your Parameters of PFILE (Directory Structure,Permissions)

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

PRVF-0002: Could not retrieve local node name

[root@rac3 ~]# hostname

rac3.dell.com

[root@rac3 ~]# cat /etc/hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1               localhost.localdomain localhost

::1             localhost6.localdomain6 localhost6

[root@rac3 ~]# 

Solution: add hostname details in host file

[root@rac3 ~]# 

[root@rac3 ~]# cat /etc/hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1               localhost.localdomain localhost

::1             localhost6.localdomain6 localhost6

[root@rac3 ~]# [root@rac3 ~]# cat /etc/hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1               localhost.localdomain localhost

::1             localhost6.localdomain6 localhost6

##-- Public-IP

192.168.1.11    rac1.dell.com   rac1

192.168.1.12    rac2.dell.com   rac2

192.168.1.13    rac3.dell.com   rac3

##-- Private-IP

10.0.0.11 rac1-priv.dell.com rac1-priv

10.0.0.12 rac2-priv.dell.com rac2-priv

10.0.0.13 rac3-priv.dell.com rac3-priv

##-- Virtual-IP

192.168.1.21 rac1-vip.dell.com rac1-vip

192.168.1.22 rac2-vip.dell.com rac2-vip

192.168.1.23 rac3-vip.dell.com rac3-vip

##-- SCAN IP

192.168.1.30 dellc-scan.dell.com dellc-scan

192.168.1.31 dellc-scan.dell.com dellc-scan

192.168.1.32 dellc-scan.dell.com dellc-scan

##-- Storage-IP

192.168.1.40    san.dell.com    san

[root@rac3 ~]# 

Topics related to Database:

RMAN DATAGAURD CONFIGURATION

Data Guard Config for RAC Database.

Active dataguard configuration with Example

Configure Flashback Database in 12.1 RDBMS

Database Link creation form PDB to NON-PDB

RMAN

This Article explains how to install ORACLE DATABASE in Linux Environment.

Thanks for Reading

Regards,

Mohammed Areefuddin.

Suggested Topics :

Linux	DATABASE	RMAN	RAC	EBS
5.7 Install	DATABASE Install		11201 Install	R1226 Install
Open Filer Instl	Active DataGaurd	Rman DataGuard	12102 RAC INSTAL	R1229 M7 Clone
DNS in Linux 7	WIN-LINUX MIGRATE	RMAN DB CLONE	RAC DataGuard	Pluggable DB Clone
lpadmin cups	ASMCMD ASM Migrt	RMAN ASM Migrt	RAC DB Clone	appsutil for DB
	11204 M-Upgrade		RAC - RAC Clone	JDK JRE upgrade
	12102 M-Upgrade		12C-19C RAC UPGRADE	Add EBS Node
	Flashback DB			ADOP in R12.2.9
				12102 EBS UPGRADE
Linux Basics	DATABASE Basics	RMAN Basics	RAC Basic	EBS Basics