DBCLI

DBCLI COMMAND

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

dbcli - It is a command line interface available on bare metal and virtual machine DB systems.

The database CLI commands must be run as the root user

dbcli is in the /opt/oracle/dcs/bin/ directory.

Oracle Database maintains logs of the dbcli command output in the dcscli.log and dcs-agent.log files in the /opt/oracle/dcs/log/ directory.

The database CLI commands use the following syntax:

EX::dbcli command [parameters]

command is a verb-object combination such as create-database.

parameters include additional options for the command. Most parameter names are preceded with two dashes, for example, --help. Abbreviated parameter names are preceded with one dash, for example, -h.

CLIADM

======

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

Syntax:cliadm update-dbcli [-h] [-j]

h for help

j for json format

Note:On RAC DB systems, execute the cliadm update-dbcli command on each node in the cluster.

AgentCommands

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

The following commands are available to manage agents:

dbcli ping-agent

dbcli list-agentConfigParameters

dbcli update-agentConfigParameters

Clean/purge logs

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

The following commands are available to manage policies for automatic cleaning (purging) of logs.

dbcli create-autoLogCleanPolicy

dbcli list-autoLogCleanPolicy

Backup with dbcli

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

Before you can back up a database by using the dbcli create-backup command, you'll need to:

Create a backup configuration by using the dbcli create-backupconfig command.

Associate the backup configuration with the database by using the dbcli update-database command.

After a database is associated with a backup configuration, you can use the dbcli create-backup command in a cron job to run backups automatically.

Commands for Backup:

dbcli create-backup

dbcli getstatus-backup

dbcli schedule-backup

Database Commands(The dbcli create-database command is available on bare metal DB systems only)

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

The following commands are available to manage databases:

dbcli clone-database

dbcli create-database

dbcli delete-database

dbcli describe-database

dbcli list-databases

dbcli modify-database

dbcli recover-database

dbcli register-database

dbcli update-database

Objectstoreswift Commands

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

You can back up a database to an existing bucket in the Oracle Cloud Infrastructure Object Storage service by using the dbcli create-backup command, but first you'll need to:

Create an object store on the DB system, which contains the endpoint and credentials to access Object Storage, by using the dbcli create-objectstoreswift command.

Create a backup configuration that refers to the object store ID and the bucket name by using the dbcli create-backupconfig command.

Associate the backup configuration with the database by using the dbcli update-database command.

The following commands are available to manage object stores.

dbcli create-objectstoreswift

dbcli describe-objectstoreswift

dbcli list-objectstoreswifts

Objectstoreswift Commands

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

You can back up a database to an existing bucket in the Oracle Cloud Infrastructure Object Storage service by using the dbcli create-backup command, but first you'll need to:

Create an object store on the DB system, which contains the endpoint and credentials to access Object Storage, by using the dbcli create-objectstoreswift command.

Create a backup configuration that refers to the object store ID and the bucket name by using the dbcli create-backupconfig command.

Associate the backup configuration with the database by using the dbcli update-database command.

The following commands are available to manage object stores.

dbcli create-objectstoreswift

dbcli describe-objectstoreswift

dbcli list-objectstoreswifts

Rmanbackupreport Commands

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

The following commands are available to manage RMAN backup reports:

dbcli create-rmanbackupreport

dbcli delete-rmanbackupreport

dbcli describe-rmanbackupreport

dbcli list-rmanbackupreports

Schedule Commands

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

The following commands are available to manage schedules:

dbcli describe-schedule

dbcli list-schedules

dbcli update-schedule

dbcli list-scheduledExecutions:Use the dbcli list-scheduledExecutions command to list scheduled executions.

Patching Commands:

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

Use the dbcli update-server command to apply patches to the server components in the DB system. For more information about applying patches, see Patching a DB System.

dbcli update-server

TDE Commands

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

The following commands are available to manage TDE-related items (backup reports, keys, and wallets):

dbcli list-tdebackupreports

dbcli update-tdekey

dbcli recover-tdewallet

Admin Commands

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

The following commands are to perform administrative actions on the DB system:

dbadmcli manage diagcollect

dbadmcli power

dbadmcli power disk status

dbadmcli show controller

dbadmcli show disk

dbadmcli show diskgroup

dbadmcli show env_hw (environment type and hardware version) (environment type and hardware version)

dbadmcli show fs (file system details) (file system details)

dbadmcli show storage

dbadmcli stordiag

RDS -DBA TASKS

Doc:

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.html

Difference between apps and applsys

Apps is centralised schema for all products in oracle.it has only access of views, synonyms...etc.

Applsys is only used to Handel FND and AOL other products base tables (ap,ar...etc)which required for selected responsibility is is valid or not after user clicked on particular responsibility.

Applsyspub/pub is dB user which is used to authenticate our Sso/anyuser when we login to EBS.

Guest user is dummy user which connects jdbc driver to connect users which doesn't have any roles.ex--istore,iprocurement..etc.

Role of APPLSYSPUB user/schema in Oracle Applications:

When we login to applications,initially oracle applications connect to public schema, APPLSYSPUB. This schema has sufficient privileges to perform the authentication of an Applications User (FND user), which includes running PL/SQL packages to verify the username/password combination and the privilege to record the success or failure of a login attempt.

The public ORACLE username and password that grants access to the Oracle E-Business Suite initial sign-on form. The default is APPLSYSPUB/PUB.

Once we change the APPLSYSPUB password must propagate the change to application tier configuration files. If the instance is Autoconfig enabled, must edit the CONTEXT file on each tier prior to running Autoconfig.

In the CONTEXT file, locate the autoconfig variable “s_gwyuid_pass” and set it to the new password, then run AutoConfig in each applications nodes.

When Autoconfig is not being used:

If you are not using Autoconfig you must manually edit the following configuration files :

1) FND_TOP/resource/appsweb.cfg
2) OA_HTML/bin/appsweb.cfg
3) FND_TOP/secure/HOSTNAME_DBNAME.dbc

To change password of APPLSYSPUB with FNDCPASS:

$FNDCPASS APPS/[apps_pass] 0 Y SYSTEM/[system_pass] ORACLE APPLSYSPUB [new_passs].

0 & Y are flags for FNDCPASS
0 is request id (request ID 0 is assigned to request ID's which are not submitted via Submit Concurrent Request Form)
'Y' indicates that this method is directly invoked from the command-line and not from the Submit Request Form.

All application tier processes (Apaches) must be restarted following the password change.

Role of GUEST user/schema in Oracle Applications:

GUEST is a dummy schema.

By default it has ORACLE as password.

GUEST/ORACLE password is present in DBC file at $FND_TOP/secure directory as well as at $FND_TOP/secure/SID_hostname directory.

If a user logs in without any role mappings, the user will get the Guest role, which has a default permission of "R".

GUEST user is used by JDBC Drivers and Oracle Self Service Web Applications like istore, irecruitment, iprocurement, ipayables, ireceivables etc to make initial Connection.

Role of APPLSYS & apps user/schema in Oracle Applications:

APPLSYS user is same as other oracle users like AP, AR, GL etc which hold their set of tables, views etc. In the same manner APPLSYS Account holds its set of tables like FND_USER and FND_APPLICATION, AD_APPLIED_PATCHES etc.

Applsys schema has applications technology layer products like FND and AD etc.

Apps is a universal schema, it has synonyms to all base product tables and sequences. This also has code objects for all products (triggers, views, packages, synonyms etc).

APPS is central Schema which holds synonyms for all other Users Database Objects.

Note: APPLSYS and APPS should have same password.

Reason why these contains same password.

Both apps & applsys need to have same password because when you sign on to apps, initially it connects to a public schema called APPLSYSPUB. This validates AOL name and password that we enter (operations/welcome). Once this is verified we select responsibility, this is validated by APPLSYS schema and then it connects to apps schema.

During signon process it uses both applsys and apps, hence this expects both the password to be identical. If the password for applsys & apps are not identical (Different) Try changing apps password to something else and try to login, the validation at the last stage would fail. This would result in failure of application login.

Difference B/W APPLSYSPUB & GUEST:

APPLSYSPUB/PUB - is DB user which is used by any utility to retrieve APPS schema password for further logins.

GUEST/ORACLE - is EBS user with no or max limited privileges to execute authorization function.

Startup Upgrade Mode

What does the "startup upgrade" command do? How is the startup upgrade different from a normal startup?

Answer: Starting in 10g, the "startup upgrade" command is used during upgrade procedures. It differs from a normal startup because only certain operations are permitted. Once the database is started in upgrade mode, only queries on fixed views execute without errors until after the catctl.pl script is run. Before running catctl.pl, queries on any other view or the use of PL/SQL returns an error.

Start the database in upgrade mode for a multitenant container database (CDB):

SQL> alter pluggable database all open upgrade;

For a non-CDB issue this startup command:

SQL> startup upgrade

Pre-upgrade checks include:

SQL> STARTUP UPGRADE
SQL> SPOOL pre_upgrade_check.log
SQL> @?/rdbms/admin/utlu111i.sql
SQL> SPOOL OFF

########################

[oracle3@servername admin]$ cat utlip.sql

Rem

Rem NAME

Rem utlip.sql - UTiLity script to Invalidate Pl/sql

Rem

Rem DESCRIPTION

Rem

Rem *WARNING* *WARNING* *WARNING* *WARNING* *WARNING* *WARNING*

Rem Do not run this script directly.

Rem

Rem utlip.sql is automatically executed when required for database

Rem upgrades.

Rem Use utlirp.sql if you are looking to invalidate and recompile

Rem PL/SQL for a 32-bit to 64-bit conversion. Use dbmsupgnv.sql

Rem to convert all PL/SQL to NATIVE or dbmsupgin.sql to convert all

Rem PL/SQL to INTERPRETED.

Rem

Rem *WARNING* *WARNING* *WARNING* *WARNING* *WARNING* *WARNING*

################

Also will share one issue,when we noticed multiple packages or plsql objects are getting invalid frequently then we can acutlay do the below sinario.

Stratup upgrade-->run utlirp.sql to make all plsql objects are invalidate-->then stratup normal mode -->run utlrp.

it will fix the issue.

OCI-CPU-PATCHING

Installing Database Patch Updates VM DB System in Oracle Cloud Infrastructure

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

dbcli - It is a command line interface available on bare metal and virtual machine DB systems.

It is applied for:(when you choose DB SYSTEM only)

Oracle Database 19.0.0.0.0

Oracle Database 12.1.0.2

Oracle Database 11.2.0.4

Steps:

=====

1.We have to check patch update is available.

2.Prepare for installation of the patch update

3.APPLY Patch applied.

4.Post steps.

Step1:

When a patch update becomes available, it appears in the following locations for an Single Instance VM DB System:

Object Storage Service – dbcli

Oracle Cloud Infrastructure DB Systems Console - BUT ORACLE RECOMENDS ONLY dbcli

For install the latest cloud tooling update

cliadm update-dbcli

dbcli update-server --precheck







dbcli update-server

Updating DB HOME dbcli list-dbhomes



Apply Database Patch Update
rm -rf /tmp/datapatchoutput*





Note2360215:Oracle Database 19.0.0.0.0 Release Update (RU) or Oracle Database 12.1.0.2 Bundle Patches (BP) or Oracle Database 11.2.0.4 Patch Set Updates (PSU) are automatically included when you create a new Single Instance VM DB System.

SERVER HARDENING

Secops team will use some third party tools and when they run they will get risk level of CVE’s in 3 types.

Depending on CVSS score level of software,they will be divied in below catagiroies. As per the below link , for Oracle E-Business Suite:
https://www.inoapps.com/insights/news/oracle-has-released-their-third-cpu-of-2020-heres-your-guide-to-the-latest-updatesA maximum reported CVSS Base Score of 9.1, indicating critical vulnerability anything below that is non critical.

Critical

High

Medium

Low

Provide CVE CODE as provided below

We need to validate each CVE with the below oracle note id/read me of PSU

Also we get list from the below

https://www.rapid7.com/db/vulnerabilities/oracle-weblogic-cve-2020-5398

Also When you validate need to check BASE SCORE

Validated CVE with PSU noteid

CVE-2017-5645 JAN-2018

CVE-2018-11058 JUL-2019

CVE-2020-2966 JUL-2020

CVE-2020-2967 JUL-2020

CVE-2020-5398 JUL-2020

CVE-2020-9546 JUL-2020

CVE-2020-14557 JUL-2020 SUpported version after 12.1

CVE-2020-14572,JUL-2020

CVE-2020-14588 JUL-2020

CVE-2020-14589 JUL-2020

CVE-2020-14622 JUL-2020

CVE-2020-14625 JUL-2020

CVE-2020-14644 JUL-2020

CVE-2020-14645 JUL-2020

CVE-2020-14652,JUL-2020

CVE-2020-14687 JUL-2020

EBS: CPU NOTEID

https://updates.oracle.com/Orion/Services/download?type=readme&aru=23587565

Notes: https://www.oracle.com/security-alerts/cpujul2020.html

1. Outside In Technology is a suite of software development kits (SDKs). The protocol and CVSS score depend on the software that uses the Outside In Technology code. The CVSS score assumes that the software passes data received over a network directly to Outside In Technology code, but if data is not received over a network the CVSS score may be lower.

CPU -PATCH ANALSYS

REDIS WITH PHP APPLICATION...

A website/application has own challges even it is successed, when it touches high number of users.
simillarly we faced one issue that an application is reciving huge users at few times but not all times.
obvisoly we planned application much more reliable and consistent. If the term scalability crossed your mind, you've got the right idea.

scalability is the ability of a system to handle an increased amount of traffic or processing and accommodate growth while maintaining a desirable user experience.
There are basically two ways of scaling a system: vertically, also known as scaling up,(increasing system configuration with memory and hard disk i can say)
and horizontally, also known as scaling out.(just deploying another server to share the exissitng load)

vertically scaling has no issue but it has it own challenges like only mem or infra will be changed its not suitable for all secanarios.

we have problem here when we do harizonal scaling

When user A makes a request to mydomain.com, the load balancer will forward requests to server1. User B, on the other hand, gets forwarded another node from the cluster, server2.

What happens when user A makes changes to the application, like uploading files or updating content in the database? How do you maintain consistency across all nodes in the cluster? Further, PHP saves session information in disk by default. If user A logs in, how can we keep that user's session in subsequent requests, considering that the load balancer could send them to another server in the cluster?

Need to maintain user session in common storage devise then only it can have more accessability

****************REDIS IS SOLUTION FOR THIS********

Redis.
Redis is an in-memory data structure store, used as a database, cache and message broker. It stores data in key-value which gives Redis more flexibility and performance.

Prerequisites
Before we start this tutorial you need to have complete access to your server we need to install Redis server and also add some required PHP extension.

Installing and configure Redis
Step 1. Login to your server via terminal or putty if you are using the window.

Step 2. Once login successfully runs bellow commands.

sudo apt update
sudo apt install redis-server
Step 3. Check if Redis is installed successfully using below command

sudo systemctl status redis
Configure Redis sever with PHP
Step 4. Now we can check Redis server is functioning correctly, we can use redis-cli, Redis CLI is a command-line tool for Redis.

redis-cli
Redis cache with PHP

Step 5. Now if you want to restart Redis server run this command

sudo systemctl restart redis
Or, if you want to clear Redis cache you can run this command

redis-cli flushall
Configure Redis for PHP extension
To use Redis with your PHP application we need an extension that connects PHP with Redis to do that we need to run below command.

sudo pecl install redis
sudo apt-get install php-redis
After this, we need to restart our PHP using below command

sudo service php7.0-fpm restart
Now once your PHP restarts you are ready to use the Redis server.

***FIRST method to add cache as REDIS***
ubuntu@ip-172-31-37-25:/etc/php/7.2/cli$ pwd
/etc/php/7.2/cli
ubuntu@ip-172-31-37-25:/etc/php/7.2/cli$ grep session.save_handler php.ini
session.save_handler = files
ubuntu@ip-172-31-37-25:/etc/php/7.2/cli$ ###MAKE THIS ENTRY AS redis###
ubuntu@ip-172-31-37-25:/etc/php/7.2/cli$ session.save_handler = redis
session.save_handler: command not found
ubuntu@ip-172-31-37-25:/etc/php/7.2/cli$ grep session.save_path php.ini
; session.save_path = "N;/path"
; session.save_path = "N;MODE;/path"
;session.save_path = "/var/lib/php/sessions"
; (see session.save_path above), then garbage collection does *not*
ubuntu@ip-172-31-37-25:/etc/php/7.2/cli$ hostname
ip-172-31-37-25

****second method to add cache as REDIS***
Add Redis as Cache in LAVAPHP 3 Application
If you want to use Redis as a cache for your LAVAPHP 3 Application you update your cache setting in config/app.php and update your cache setting with this

'Cache' => [
'default' => [
'className' => 'Redis',
'path' => CACHE,
'password' => false,
'server' => '127.0.0.1',
'port' => 6379,
],
'_cake_core_' => [
'className' => 'Redis',
'prefix' => 'cake_redis_core_',
'path' => CACHE . 'persistent/',
'serialize' => true,
'duration' => '+1 years',
'server' => '127.0.0.1',
'port' => 6379,
'password' => false,
],
'_cake_model_' => [
'className' => 'Redis',
'prefix' => 'cake_redis_model_',
'path' => CACHE . 'models/',
'serialize' => true,
'duration' => '+1 years',
'server' => '127.0.0.1',
'port' => 6379,
'password' => false,
],
],

Also for second method you can check the https://www.digitalocean.com/community/tutorials/how-to-set-up-a-redis-server-as-a-session-handler-for-php-on-ubuntu-16-04

DIFFERENCE BETWEEN SYS and SYTEM

The following administrative user accounts are automatically created when you install Oracle Database. They are both created with the password that you supplied upon installation, and they are both automatically granted the DBA role.

SYS

This account can perform all administrative functions. All base (underlying) tables and views for the database data dictionary are stored in the SYS schema. These base tables and views are critical for the operation of Oracle Database. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by the database. They should never be modified by any user or database administrator. You must not create any tables in the SYS schema.

The SYS user is granted the SYSDBA privilege, which enables a user to perform high-level administrative tasks such as backup and recovery.
SYSTEM

This account can perform all administrative functions except the following:

Backup and recovery
Database upgrade

`SYS`

Automatically created when Oracle database is installed
Automatically granted the DBA role
Has a default password: CHANGE_ON_INSTALL (make sure you change it)
Owns the base tables and views for the database data dictionary
The default schema when you connect as SYSDBA

Tables in the SYS schema are manipulated only by the database. They should never be modified by any user or database administrator, and no one should create any tables in the schema of user SYS. Database users should not connect to the Oracle database using the SYS account.

`SYSTEM`

Automatically created when Oracle database is installed
Automatically granted the DBA role
Has a default password: MANAGER (make sure you change it)
Used to create additional tables and views that display administrative information
Used to create internal tables and views used by various Oracle database options and tools

Never use the SYSTEM schema to store tables of interest to non-administrative users.

ssl certification updation after expiry using orapk

### Check wallet and OHS home locations ###
[user@servername ~]$ export ORACLE_HOME=/xxxq1/xxxapp/ohshome
[user@servername ~]$ cd $ORACLE_HOME/wallet_bisuctproject
[user@servername wallet_bisuctproject]$ ls
AddTrustExternalCARoot.ccc cwallet.sso ewallet.p12 servername_cloud_ge_com.ccc TrustedSecureCertificateAuthority5.ccc
certs cwallet.sso.lck ewallet.p12.lck server.csr USERTrustRSAAddTrustCA.ccc

[user@servername wallet_bisuctproject]$ cat server.csr
----BEGIN NEW CERTIFICATE REQUEST-----
MIICyDCCAbACAQAwgYIxCzAJBgNVBAYTAlVTMQswCQYDVQQIEwJNQTEPMA0GA1UE
BxMGQm9zdG9uMRgwFgYDVQQKEw9CYWtlciBIdWdoZXMgR0UxFzAVBgNVBAsTDkVu
dGVycHJpc2UgU1NMMSIwIAYDVQQDExlvZ2VycGwzOTE1cXYuY2xvdWQuZ2UuY29t
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWEEEEEEEEEEEEEEEEEEEEEEEEEE9o2V
wtgfJd9Fbd9fonxizbzxKhEzQ3i0yAins48iEC00dMUF+AoG1ETgyyPV5FrGaGUT
iimgdLsiFnRqDonmgHORmfDcakW6AJJT/AtwFXaRRhc6q3GMZxu3e3q5aGSHL/7P
Ce9ArmykeLWrNocuOiuM3tD3H830vWmJMKaEQBdhKKJQNONymruvBKPvXXWul52r
AY8+ohjTJ12Eh4ndLimyikq2HYk7Sh1Ci/QrqNiGM2P5MEot7w+9Ng57gpp0Xxzp
yHTWnc6RuH2YggNGEb5Sy1y2JQDVClFYrRLbwI1FC5iIskFHST4nXXT8Q6E=
-----END NEW CERTIFICATE REQUEST-----

###CHECK WALLET HOME LOCATION###
cd $ORACLE_HOME/wallet_bisuctproject

###MOVE OLD CERTS directory and CREATE NEW CERTS DIRECTORY######
mv -i certs certsold
mkdir certs

###COPY GIVEN CERTIFICATES TO THIS FOLDER####
[user@servername wallet_bisuctproject]$ cd certs
[user@servername certs]$ ls
AddTrustExternalCARoot.ccc servername_cloud_ge_com.ccc TrustedSecureCertificateAuthority5.ccc USERTrustRSAAddTrustCA.ccc
[user@servername certs]$ pwd
/xxxq1/xxxapp/ohshome/wallet_bisuctproject/certs

###TO ADD TRUST CERTIFICATES####
[user@servername certs]$
$ORACLE_HOME/oracle_common/bin/orapki wallet add -wallet $ORACLE_HOME/wallet_bisuctproject -trusted_cert -cert $ORACLE_HOME/wallet_bisuctproject/certs/USERTrustRSAAddTrustCA.ccc -pwd welcomegrc123
946 $ORACLE_HOME/oracle_common/bin/orapki wallet add -wallet $ORACLE_HOME/wallet_bisuctproject -trusted_cert -cert $ORACLE_HOME/wallet_bisuctproject/certs/TrustedSecureCertificateAuthority5.ccc -pwd welcomegrc123
947 $ORACLE_HOME/oracle_common/bin/orapki wallet add -wallet $ORACLE_HOME/wallet_bisuctproject -user_cert -cert $ORACLE_HOME/wallet_bisuctproject/certs/servername_cloud_ge_com.ccc -pwd welcomegrc123

####TO DISPLAY WATLET INFORMATION###
[user@servername ~]$ $ORACLE_HOME/oracle_common/bin/orapki wallet display -wallet $ORACLE_HOME/wallet_bisuctproject -summary -pwd welcomegrc123
Oracle PKI Tool : Version 12.2.1.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
Subject: CN=servername.domainname.com,OU=Enterprise SSL,O=BISCUT COMPANY ,L=Boston,ST=MA,C=US
User Certificates:
Trusted Certificates:
Subject: CN=Trusted Secure Certificate Authority 5,O=Corporation Service Company,L=Wilmington,ST=DE,C=US
Subject: CN=AddTrust External CA Root,OU=AddTrust External TTP Network,O=AddTrust AB,C=SE
Subject: CN=USERTrust RSA Certification Authority,O=The USERTRUST Network,L=Jersey City,ST=New Jersey,C=US

OCI ERP Administration | Automation Hub ERP | OCI Architecture | ERP Admin | Cloud Automation)

Labels

DBCLI

RDS -DBA TASKS

Difference between apps and applsys

Role of APPLSYSPUB user/schema in Oracle Applications:

Role of GUEST user/schema in Oracle Applications:

Role of APPLSYS & apps user/schema in Oracle Applications:

Startup Upgrade Mode

OCI-CPU-PATCHING

Apply Database Patch Update

SERVER HARDENING

CPU -PATCH ANALSYS

REDIS WITH PHP APPLICATION...

DIFFERENCE BETWEEN SYS and SYTEM

`SYS`

`SYSTEM`

ssl certification updation after expiry using orapk

OCI-APPS CLONE

Followers