Oracle Exadata is a highly integrated, scale-out engineered system designed specifically for extreme database performance.
Exadata Smart Scan: Offloads SQL processing (e.g., row filtering, column projection, and AI/ML vector search) directly to the storage servers. Only the required result sets are sent to the database servers, drastically reducing network traffic and compute bottlenecks.
Public IP: The public IP address is for the server. This is the same as any server IP address, a unique address with exists in /etc/hosts.
Private IP: Oracle RAC requires “private IP” addresses to manage the CRS, the clusterware heartbeat process and the cache fusion layer.
Virtual IP: Oracle uses a Virtual IP (VIP) for database access. The VIP must be on the same subnet as the public IP address. The VIP is used for RAC failover (TAF).
Networking Files:
The "/etc/sysconfig/network" file holds top-level networking configuration, including the hostname and gateway settings.
The "/etc/sysconfig/network-scripts/ifcfg-eth0" file holds the network configuration for the "eth0" adapter. If you have multiple network adapters, you would expect additional configuration files (eth1, eth2 etc.). The following example contains the configuration for a DHCP adapter.
The "/etc/hosts" file contains information for local name resolution.
The "/etc/resolv.conf" file is used to configure the location of the DNS servers to be used for name resolution. There can be multiple "nameserver" lines, one for each nameserver.
The "/etc/sysconfig/network-scripts" directory contains a number of network related scripts and commands. The ifdown and ifup commands are used to stop and start the specified network adapters.
The current network settings are displayed using the ifconfig command.
https://serhatcelik.wordpress.com/2021/02/15/step-by-step-oracle-19c-rac-installation-on-oracle-linux-7-9/
https://serhatcelik.wordpress.com/2021/02/18/step-by-step-oracle-19c-rac-installation-on-oracle-linux-7-9-part-2/
https://serhatcelik.wordpress.com/2021/02/20/step-by-step-oracle-19c-rac-installation-on-oracle-linux-7-9-part-3-database/
https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html/networking_guide/sec-configuring_ip_networking_with_ifcg_files
https://oracle-base.com/articles/linux/linux-network-configuration#:~:text=The%20%22%2Fetc%2Fsysconfig%2Fnetwork%2Dscripts%2Fifcfg,configuration%20for%20a%20DHCP%20adapter.
In short: SID = the unique name of your DB, ServiceName = the alias used when connecting.
SERVICE_NAMES specifies one or more names by which clients can connect to the instance. The instance registers its service names with the listener. When a client requests a service, the listener determines which instances offer the requested service and routes the client to the appropriate instance.
SID = identifies the database instance (database name + instance number). So if your database name is somedb and your instance number is 3, then your SID is somedb3.
DB Name = Name of the database (database can be shared b/t multiple instances)
DB Domain = Usually the same as your company domain (somecompany.com)
Global Database Name = Database name + database domain (somedb.somecompany.com)
Service Name = A "connector" to one or more instances. It is often useful to create additional service names in a RAC environment since the service can be modified to use particular SIDs as primary or secondary connections, or to not use certain SIDs at all.
Service Alias = An alias to the service name (just like a CNAME, etc). Say you make your service name something meaningful to the dba, but perhaps it's a bit esoteric. Create a service alias and name it something that will be meaningful to the user.
Instance name = same as SID
The operating system starts ohasd (how):
Once the Operating system starts and finish the boot scrap process it reads /etc/init.d file via the initialisation daemon (INIT daemon >>) called init or init.d. The init tab file is the one it triggers oracle high availability service daemon
INIT daemon is very first daemon to be start at the time of the operating system boot, we can say it is system and service manager for the oracle linuxor any linux flavor. This services are start and stop through script which is stored in the /etc/init.d directory .
OHASD:-
Oracle High Availability Daemon is the introduced in oracle 11g which is manage all clusterware resources , it is responsible to start and monitor all local oracle clusterware daemon. We can say it is anchor for the all the process .
cat /etc/inittab|grep init.ohasd
h1:35:respawn:/etc/init.d/init.ohasd run >/dev/null 2>&1 </dev/null
HAS startup sequence:
– /etc/inittab
– /etc/init.d/init.ohasd
– /etc/rc.d/init.d/ohasd
– $GRID_HOME/bin/ohasd.bin
“/etc/init.d/init.ohasd” is started at runlevel 3 (networked, multi-user mode) or runlevel 5 (runlevel 3 + GUI).Logs present under “$GRID_HOME/log/<host_name>“.
/etc/inittab >>/etc/init.d/init.ohasd>>>/etc/rc.d>init.d/ohasd>>$GRID_HOME/ohasd.bin
“crsctl disable crs” updates a flag in “/etc/orale/scls_scr/<host_name>/root/ohasdstr“.
This file either contains “enable” or “disable” without any carriage return.
If “disable“, “/etc/rc.d/init.d/ohasd” will not startup.
nohup /etc/init.d/init.ohasd run &
ohasd starts agents(whic all agent):
cssdagent - Agent responsible for spawning CSSD. >>> CSSD
orarootagent - Agent responsible for managing all root owned ohasd resources.
CRSD - Primary daemon responsible for managing cluster resources.
CTSSD - Cluster Time Synchronization Services Daemon
Diskmon
ACFS (ASM Cluster File System) Drivers
oraagent - Agent responsible for managing all oracle owned ohasd resources.
MDNSD - Used for DNS lookup
GIPCD - Used for inter-process and inter-node communication
GPNPD - Grid Plug & Play Profile Daemon
EVMD - Event Monitor Daemon
ASM - Resource for monitoring ASM instances
cssdmonitor - Monitors CSSD and node health (along wth the cssdagent).
CSSDmonitor
to start up daemons (gipcd, mdnsd, gpnpd, ctssd, ocssd,crsd, evmd asm etc), and
crsd starts agents Which)
orarootagent - Agent responsible for managing all root owned crsd resources.
Network resource - To monitor the public network
SCAN VIP(s) - Single Client Access Name Virtual IPs
Node VIPs - One per node
ACFS Registery - For mounting ASM Cluster File System
GNS VIP (optional) - VIP for GNS
oraagent - Agent responsible for managing all oracle owned crsd resources.
ASM Resouce - ASM Instance(s) resource
Diskgroup - Used for managing/monitoring ASM diskgroups.
DB Resource - Used for monitoring and managing the DB and instances
SCAN Listener - Listener for single client access name, listening on SCAN VIP
Listener - Node listener listening on the Node VIP
Services - Used for monitoring and managing services
ONS - Oracle Notification Service
eONS - Enhanced Oracle Notification Service
GSD - For 9i backward compatibility
GNS (optional) - Grid Naming Service - Performs name resolution
that start user resources (database, SCAN, listener etc).
http://rafik-dba.blogspot.com/2019/02/oracle-rac-startup-sequence.html
http://oracle-help.com/oracle-rac/rac-11gr2-clusterware-startup-sequence/
http://dbaparadise.com/2021/01/what-is-local-undo-mode/
==============
Four Ways to Startup or Shutdown Your 19c Database:
There you have the 4 tools: SQLPLUS, RMAN, Cloud Control, SRVCTL!
=================
What Is Local Undo Mode:
12c database (release 1)>>shared undo mode>>undo tablespace was shared by all the PDBs within the same CDB, meaning there was only one UNDO tablespace for the instances.
with 12cR2, the local undo mode was introduced>>each PDB has their own UNDO tablespaces.This is required by a few Oracle features, ie. hot cloning.
Here are some of features that I came across, that require local undo mode: PDB hot cloning, creating refreshable PDBs, near zero downtime PDB relocation, proxy PDBs.
If the database (the CDB) is created with DBCA, then local undo mode is the default mode. If the database (the CDB) is created with the CREATE DATABASE statement, then shared undo mode is the default.
In the latter case, you need to use the undo_mode_clause and specify LOCAL UNDO ON explicitly, if you want to create a database with local undo mode.
How to determine what undo mode the database is in? The answer is in the database_properties view
col property_name for A20
col property_value for A20
select property_name, property_value from database_properties where property_name like '%UNDO%';
Another method:
select con_id, tablespace_name from cdb_tablespaces where contents like '%UNDO%' order by 1;
How to switch to local undo mode?
it requires an outage.
sqlplus / as sysdba
shutdown immediate;
startup upgrade
alter database local undo on;
shutdown immediate;
startup;
To switch to shared undo mode
sqlplus / as sysdba
shutdown immediate;
startup upgrade;
alter database local undo off;
shutdown immediate;
startup;
Note that the local undo tablespace inside the PDBs will not be dropped by Oracle and it will not be used either. For clarity purposes it is recommended to drop them manually after you switch the database to shared undo mode.
======================
MAX_PDBS parameter:
Introduced in 12cR2.
To limit the number of pluggable databases (PDBs) one can create in a CDB or in an application root container.
the PDB$SEED, Application Seed and Application Root are ignored from the count.
By default this parameter’s value is 4098
Why would you want to limit the number of PDBs a user can create in the CDB?
One of the reasons is licensing!
In 12c version you can have 1 user created PDB, without paying for the Multitenant option. In 19c you can have up to 3 user created PDBs in a CDB, without paying for the Multitenant option.
Once the parameter is set, if you will try to create another PDB, by accident, you will receive an error: ORA-65010: maximum number of pluggable databases created.
alter system set MAX_PDBS=3 scope=both;
==================
How To Use Datapump Between Different Database Releases:
What if the source database is 12.2 and the target database version is 11.2?>> Use Vesion version=12.1
What if the versions are the same, but the compatible parameter of the target database is lower?
Once you find out the target version/compatible parameter, running a datapump export job with the appropriate version will be easy, as Oracle is providing you with an export datapump parameter called VERSION to facilitate this process.
An export datapump dumpfile created by a lower version of Oracle will always be compatible with a higher version of Oracle.
======================
active duplicate :
Duplicating from an active database doesn’t require any RMAN backup to be taken from the source database. Actually, it reads all database structure from the source database that needs to be mounted or opened during the duplication.Although this method seems easy as we don’t need to backup source database or make it accessible for auxiliary instance anymore, it has its own disadvantages as well.
Two big disadvantages of the ACTIVE database duplication method are:
Negative performance impact on the source database. This impact applies to the whole duplication time.
High network traffic on the connection between the source and target databases.
RMAN>run{
DUPLICATE TARGET DATABASE TO HRPRD
FROM ACTIVE DATABASE;
}
1- Prepare auxiliary instance HRPRD1 on prd-db-01:
2- Enable status registration for HRPRD1 to run LISTENER:
3- Add following TNS entries to BOTH auxiliary and target tnsnames.ora file:
4- Ceate a password file for auxiliary instance HRPRD1 on prd-db-01:
5- Test connectivity to auxiliary and target instances from BOTH hosts using TNS:
6- On the auxiliary host, start RMAN and run the DUPLICATE command:
7- When step 6 finishes successfully, start HRPRD database using srvctl. You need to enable the second thread as well:
==============================
pdb restricted mode.
Here we use OPEN FORCE to leave restricted mode and go back to normal READ WRITE.
alter pluggable database ORCLPDB open force;
https://dbalifeeasy.com/category/oracle/cdbpdb/
Under the guidance of Oracle Support for specific purpose, the following instructions can be followed to open seed database “PDB$SEED” in “READ WRITE” mode.
Set hidden parameter “_oracle_script” at session level
SQL> alter session set "_oracle_script"=TRUE;
https://dbalifeeasy.com/2018/01/22/how-to-remove-pdb-from-restricted-mode/
SQL> select INST_ID,NAME,OPEN_MODE,RESTRICTED
from gv$pdbs order by 1,2;
Check PDB_PLUG_IN_VIOLATIONS.
select status, message, action
from pdb_plug_in_violations
where status !='RESOLVED';
===================================
Basic use of the file (Text Based) is for redirecting the http requests to the concerned Managed/admin server for particular context root.
If in your environment any one says i am trying to hit Test and it goes to Prod/some where you need to lookout here first.
Entry would look like below :
<IfModule weblogic_module>
<Location path>
SetHandler weblogic-handler
WebLogicHost host
WeblogicPort port
</Location>
</IfModule>
R12 E-Business Suite Concurrent Processing Script Cpadmin.sh Command Line Utility Which Consolidates Existing CP Tools Into A Single Menu-Based Shell Script (Doc ID 2319585.1)
What can I do to reduce the time required for cutover?
It is important to distinguish between the time needed for the whole cutover phase, and the downtime period within the phase.
The actual downtime (during which users cannot log in) is significantly shorter than the whole phase.
To help reduce the overall time taken by cutover, you can do three things:
GOAL:
On: 12.2 version, Online Patching
Can a cutover be run, from fs1 to fs2 outside of patching cycle? For testing purposes, without applying any patch, can a cutover to switch filesystem from fs1 to fs2?
How to execute empty patching cycle, without applying any patch?
SOLUTION:
The syntax to run an empty patch cycle is:
Quick Version:
$ cd <EBS_ROOT>
$ source EBSapps.env run
$ adop phase=prepare,finalize,cutover
$ source EBSapps.env run
$ adop phase=cleanup
Complete Version:
$ cd <EBS_ROOT>
$ source EBSapps.env run
$ adop phase=prepare
$ adop phase=actualize_all
$ adop phase=finalize finalize_mode=full
$ adop phase=cutover
$ source EBSapps.env run
$ adop phase=cleanup cleanup_mode=full
Run flashgrid-cluster command to confirm that all nodes are up and no nodes are shown as Inaccessible.
Kill all ohasd.bin reboot processes on the database nodes where CRS fails to start:
ps -ef | grep "ohasd.bin reboot" | grep -v grep | awk '{print $2}' | xargs kill -9
Start CRS: crsctl start crs -wait
https://blog.pythian.com/services-not-starting-automatically-with-crs-after-reboot/
http://www.dba-oracle.com/t_rac_crs_start_failure.htm
http://www.dbaref.com/troubleshooting-rac-issues/howtotroubleshootgridinfrastructurestartupissues
https://www.thegeekdiary.com/troubleshooting-oracle-rac-node-evictions-reboots-11-2-and-above/
How to Troubleshoot Grid Infrastructure Startup Issues [ID 1050908.1]
Grid Infrastructure clusterware startup sequence, please refer to note 1053147.1
http://blog.itpub.net/21980353/viewspace-2141629/
https://dbamarco.wordpress.com/tag/clusters-2/
https://www.hhutzler.de/blog/troubleshooting-clusterware-startup-problems/
Move OCR , Vote File , ASM SPILE to new Diskgroup (Doc ID 1638177.1)
Basic steps to move to the new diskgroup :
1) Create New diskgroup(CRS) with suitable redundancy for OCR and Voting files.
2) Ensure that the new diskgroup is mounted on all cluster nodes.
3) Move OCR and Vote file from <Current diskgroup> to <CRS>.
4) Change ASM SPFILE location from <current diskgroup> to <CRS> Diskgroup.
5) Mount new <CRS> diskgroup in all nodes and restart CRS in all Nodes to Startup CRS using New SPFILE from <CRS> Diskgroup.
6) Verify mount of disks and diskgroups.
7) Ensure ALL Cluster Resources are started successfully .
8) Modify OCR backup location
=============
EXAMPLES: Detailed steps with commands to rename the diskgroup :
1) Create New CRS diskgroup
Login as SYSASM using SQLPLUS into ASM instance on one node and run below command:
Sample Command:
SQL> create diskgroup CRS normal redundancy disk 'ORCL:DISK4' ,'ORCL:DISK5' ,'ORCL:DISK6'
attribute 'compatible.rdbms'='11.2.0.0', 'compatible.asm'='11.2.0.0';
Diskgroup created.
Make sure the diskgroup is mounted on all the nodes.
NOTE:
If the diskgroup you are moving the OCR to is not online on all nodes, then the ocr.loc file on those nodes where the Diskgroup is not online will not be updated.
This will prevent CRS from restarting on those nodes that the diskgroup is not online. And the ocr.loc file may need manual intervention.
2)Ensure that the new diskgroup is mounted on all cluster nodes:
SQL> alter diskgroup CRS mount;
SQL> select name, state, type from v$asm_diskgroup;
3) Move OCR and Vote file from <Current diskgroup> to <CRS>
$ORACLE_HOME/bin/bin/ocrconfig -add +CRS
$ORACLE_HOME/bin/bin/ocrconfig -delete +DATA
$ORACLE_HOME/bin/crsctl replace votedisk +CRS
Sample Command Output:
[root@test ~]# $ORACLE_HOME/bin/ocrconfig -add +CRS
[root@test ~]# $ORACLE_HOME/bin/ocrconfig -delete +DATA
[grid@test ~]# $ORACLE_HOME/bin/crsctl replace votedisk +CRS
Successful addition of voting disk 9d351cfdbef64facbfe2d1519880ef33.
Successful addition of voting disk 302c23b19e864f92bfa68eda9045e5cc.
Successful addition of voting disk 6eeca4920acb4f8fbf6ec5a4e2b8ea7b.
Successful deletion of voting disk 32f7d65cf17d4fa3bf2932998251635f.
Successful deletion of voting disk 10c31fb0891d4f5abfb38ef34cd49f4d.
Successful deletion of voting disk 7d6f7d6480554f01bfc2621a3adb8f5f.
Successfully replaced voting disk group with +CRS.
CRS-4266: Voting file(s) successfully replaced
[root@test ~]# $ORACLE_HOME/bin/crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 9d351cfdbef64facbfe2d1519880ef33 (ORCL:DISK4) [CRS]
2. ONLINE 302c23b19e864f92bfa68eda9045e5cc (ORCL:DISK5) [CRS]
3. ONLINE 6eeca4920acb4f8fbf6ec5a4e2b8ea7b (ORCL:DISK6) [CRS]
Located 3 voting disk(s).
[root@test ~]# $ORACLE_HOME/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2804
Available space (kbytes) : 259316
ID : 1778064925
Device/File Name : +CRS
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check succeeded
4) Change ASM SPFILE location from <Current diskgroup> to <CRS> Diskgroup.
Login as SYSASM using SQLPLUS into ASM instance on one node and run below commands:
SQL> create pfile='/tmp/init/init' from spfile;
SQL> create spfile='+CRS' from pfile='/tmp/init/init';
Now GPNPTOOL will get updated with new ASM SPFILE location.
That can be verified by below command : (As grid user or root)
$ORACLE_HOME/bin/gpnptool get
5)Mount diskgroup in all RAC nodes:
SQL> alter diskgroup CRS mount;
Restart CRS in all nodes to startup CRS using new SPFILE from <CRS> diskgroup :
$> crsctl stop crs
$> crsctl start crs
6) Verify the mount of the diskgroup and its disks:
SQL> set line 1000
SQL> set pages 599
SQL> col path format a30
SQL> select name,path,group_number,header_status,total_mb,free_mb from v$asm_disk;
NAME PATH GROUP_NUMBER HEADER_STATU TOTAL_MB FREE_MB
------------------------------ ------------------------------ ------------ ------------ ---------- ----------
DATA1 ORCL:DATA1 2 MEMBER 15109 9982
DATA2 ORCL:DATA2 2 MEMBER 15109 9982
DATA3 ORCL:DATA3 1 MEMBER 15109 9982
DATA4 ORCL:DATA4 3 MEMBER 15109 9982
DISK4 ORCL:DISK4 3 MEMBER 860 576
DISK5 ORCL:DISK5 3 MEMBER 668 376
DISK6 ORCL:DISK6 3 MEMBER 668 372
SQL> select name,state,usable_file_mb,total_mb,free_mb,required_mirror_free_mb from v$asm_diskgroup;
NAME STATE USABLE_FILE_MB TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB
------------------------------ ----------- -------------- ---------- ---------- -----------------------
DATA MOUNTED 9982 15109 9982 0
CRS MOUNTED 552 2448 1400 296
7) Ensure ALL Cluster resources are started successfully using below sample commands :
$ORACLE_HOME/bin/crsctl stat res -init -t
$ORACLE_HOME/bin/crsctl check cluster -all
$ORACLE_HOME/bin/crsctl stat res -t
8) Modify the backup location for the OCR
ocrconfig -backuploc <new location>
https://dbaclass.com/article/how-to-move-voting-disk-to-another-asm-disk/
12c Database : ASM Enhancements : Password files in ASM Disk group
ASM version 11.2 allowed ASM spfile to be placed in a disk group.
In 12c we can also put ASM password file in an ASM disk group.
ASM : ASMCMD in 12c:
New ASMCMD commands, now ASMCMD has the ability to do password file management, patches on asm instances, and version
pwcopy Copies a password file to the specified location. pwcreate Creates a password file at the specified location. pwdelete Deletes a password file at the specified location. pwget Returns the location of the password file. pwmove […]
12c Database : ASM Enhancements : OCR backup to disk group
Oracle Cluster Registry (OCR) backup in ASM disk group
Before to 12c the ocr backup is located in the master node local disk at GRID_HOME/cdata/backup, but if OCR is corrupted and you have to restore you will need to find the master node and initiate the restore option.
Storing the OCR backup in an […]
VIPs:
A VIP is a Virtual IP address, and should be defined in DNS and not assigned to any host or interface.When you install GRID/ASM home, you'll specify the VIP names that were assigned in DNS. When Oracle Clusterware starts up, it will assign a VIP to each node in the cluster. The idea is, if a node goes down (crashes), clusterware can immediately re-assign that VIP to a new (surviving) node. This way, you avoid TCP timeout issues.
SCAN:
A SCAN (Single Client Access Name) is a special case of VIP. The SCAN should also be defined in DNS, and not assigned to any host or interface. There should be three IPs associated with the SCAN name in DNS, and the DNS entry should be defined so that one of the three IPs is returned each time DNS is queried, in a round robin fashion.
At clusterware startup time, each of the three VIPs that make up the SCAN will be assigned to a different node in the cluster. (Except in the special case of a two node cluster, one of the nodes wil have a 2 SCAN VIPs assigned to it.) The point of the SCAN, is that no matter how many nodes are added to or removed from the cluster, all the Net Service Name definitions in your tnsnames.ora (or LDAP equivalent) will not need to ever change, because they all refer to the SCAN, which doesn't change, regardless of how many node additions or drops are made to the cluster.
For example, in the three node cluster, you may have:
Physical and virtual hostnames/IPs assigned as follows:
Hostname Physical IP Virtual hostnmae Virtual IP
rac1 10.1.1.1 rac1-vip 10.1.1.4
rac2 10.1.1.2 rac2-vip 10.1.1.5
rac3 10.1.1.3 rac3-vip 10.1.1.6
Additionally, you may have the SCAN defined as: rac-scan with three IPs, 10.1.1.7, 10.1.1.8, 10.1.1.9. Again, the DNS definition would be defined so those IPs are served up in a round robin order.
Note that the SCAN VIPs, Host VIPs, and the Physical IPs are all in the same subnet.
Finally, though you didn't ask about it, to complete the picture, you'd also need one private, non-routable IP assigned per host, and that IP would be associated with the private interconnect. So, you may have something like:
rac1-priv 172.16.1.1
rac2-priv 172.16.1.2
rac3-priv 172.16.1.3
Note that the '-priv' addresses should not be in DNS, only in the /etc/hosts file of each host in the RAC cluster. (They are private, non-routable, and only clusterware will ever know about or use those addresses, so adding to DNS doesn't make sense.)
Note also, that '-priv' and physical IP/hostname definitions should go in /etc/hosts, and the physical IPs and VIPs should be in DNS. So, physical IPs in both DNS and /etc/hosts, VIPs only in DNS, '-priv' addresses only in /etc/hosts.
https://stackoverflow.com/questions/30219464/oracle-rac-vip-and-scan-ips
http://oracledbadmins.blogspot.com/2016/06/f.html
Force = even if someone says nologging, then we will log the *normal* redo information
=In Force logging mode Oracle database must write the redo records even when NOLOGGING is used with DDL Statements.
=It will force the write of REDO records even when no-logging is specified.
=If we write the NOLOGGING option with DDLs command then our database will not generate redo for that DDLs but in case of Dataguard or media recovery has negative impact of it. So before implement Dataguard or standby server we need to enable the FORCE LOGGING mode of the Oracle Database.So every changes should be recorded and updated in standby server while syncing.
Suplemental = we log *more* than the normal redo information into the redo logs so that GG has more to work with.
NOLOGGING can be used to minimize the amount of redo generated by Oracle. Only the following operations can make use of nologging:
SQL*Loader in direct mode
INSERT /*+APPEND*/ ...
CTAS
ALTER TABLE statements (move/add/split/merge partitions)
CREATE INDEX
ALTER INDEX statements (move/add/split/merge partitions)
starting with 18c you have 2 more possibilities
The olsnodes command provides the list of nodes and other information for all nodes participating in the cluster.You can use this command to quickly check that your cluster is operational, and all nodes are registered as members of the cluster. This command also provides an easy method for obtaining the node numbers.
oracle@vgeb07hr:/dev/mapper [+ASM1]# olsnodes -h
Syntax:
olsnodes [[-n] [-i] [-s] [-t] [node_name | -l [-p]] | [-c]] [-g] [-v]
Command Description
-n Lists all nodes participating in the cluster and includes the assigned node numbers.
-i Lists all nodes participating in the cluster and includes the Virtual Internet Protocol (VIP) address assigned to each node.
-s Displays the status of the node: active or inactive.
-t Displays node type: pinned or unpinned.
node_name Displays information for a particular node.
-l [-p] Lists the local node and includes the private interconnect for the local node. The -p option is only valid when you specify along with the -l option.
-c Displays the name of the cluster.
-g Logs cluster verification information with more details.
-v Logs cluster verification information in verbose mode. Use in debug mode and only at the direction of My Oracle Support.
To find all node in clusters:
# olsnodes -i -n -s -t
To find local node name with private interconnect.
olsnodes -l -p
To find cluster name:
olsnodes -c
Refrence:
http://oracle-help.com/oracle-rac/olsnodes-command-rac/
https://dbaclass.com/article/olsnodes-commands-rac/
Why do you need GUEST/ORACLE To connect to database?
GUEST account is used to obtain the decrypted value of the apps password for internal processes (i.e. when there is a need to connect as apps internally).
when the account gets locked/end-dated then you will see a blank page when you try to login to the instance.In that scenario you will have to correct this situation from the back-end as you will not be able to login to the application.
*******
The GUEST user account is used in the application internally ( it is an application user). One of the major needs of this account is when there is a need to decrypt the APPS password (which is stored in an encrypted format in the apps tables). In order to decrypt the APPS password, the GUEST username/password is used to accomplish this task (using “Guest User Password” profile option).
GUEST account is used to obtain the decrypted value of the apps password for internal processes (i.e. when there is a need to connect as apps internally).
when the account gets locked/end-dated then you will see a blank page when you try to login to the instance.In that scenario you will have to correct this situation from the back-end as you will not be able to login to the application.
You will not find much details about the GUEST account documented anywhere ( may be coz of security reasons).
Key points :
s_guest_user is GUEST and s_guest_pwd is EXPORT in adconfig xml file.
select fnd_web_sec.validate_login(‘GUEST’,’ORACLE’) FROM DUAL; ( to validate if guest user is corrrect)
select fnd_profile.value(‘GUEST_USER_PWD’) from dual; (to find the current guest user password)
Check the GUEST/ORACLE password is present in DBC file at $FND_TOP/secure directory as well as at $FND_TOP/secure/SID_hostname directory.
What happen if “alter user apps identified by password” is fired for apps user?
We cannot change apps password through alter user statement because Oracle Application use APPS PASSWORD to encrypt end users password in FND_USER and oracle users password in FND_ORACLE_USERID. So using FNDCPASS to change password of APPS, changes the column encrypted_oracle_password in these two tables, but alter dont do this actions. FNDCPASS update DBA_USERS table as well.While when you run alter user apps identified by password it will update only DBA_USERS.
If you have mistakenly did used alter user, you may see below error:
APP-FND-01496: Cannot access application ORACLE password
Cause: Application Object Library was unable access your ORACLE password.
It is very difficult to recover the application at this stage.
What is Actualize all, How it works?
When the number of old database editions reaches 25 or more, we should consider dropping all old database editions by running the adop actualize_all.
What is difference between finalize_mode=full & finalize_mode=quick ?
$ adop phase=finalize
$ adop phase=finalize finalize_mode=quick == This is Default Mode
$ adop phase=finalize finalize_mode=full
Finalize can be run in 2 modes: “QUICK” or “FULL”
FULL gathers database dictionary statistics (not transaction tables statistics)
QUICK skips gathering database dictionary statistics.
How to change weblogic password from Backend in EBS 12.2.x?
On EBS 12.2.X environments , if you are on R12.AD.C.Delta.7 and R12.TXK.C.Delta.7 or later, we can change Weblogic admin server password using the below script.
1. Startup admin server. Do not start any other services
$. ./EBSapps.env RUN
$ cd $ADMIN_SCRIPTS_HOME
$adadminsrvctl.sh start
2. Run the below script
perl $FND_TOP/patch/115/bin/txkUpdateEBSDomain.pl -action=updateAdminPassword
[The script will prompt for Current and New Weblogic Password and restarts Weblogic admin server using the new password.]
3. Run fs_clone to propagate the changes to the alternate file system (Patch File system).
How to Update APPS password in EBS Datasource (EBS 12.2.x)?
From EBS 12.2.x versions, whenever we change apps password we need to update the new apps password in EBS Datasource.
Otherwise managed servers wont come up.
Steps to update apps password in Weblogic datasource for EBS 12.2x, post AD-TXK 7:
1. Start Only Admin server,Do not start any other services
$. ./EBSapps.env RUN
$ cd $ADMIN_SCRIPTS_HOME
$adadminsrvctl.sh start
2. Update apps password in Datasource from backend:
$perl $FND_TOP/patch/115/bin/txkManageDBConnectionPool.pl
When prompted select the "updateDSPassword" option.
Sample screen output:
perl $FND_TOP/patch/115/bin/txkManageDBConnectionPool.pl
Please select from list of valid options
updateDSPassword - Update WebLogic Datasource Password
updateDSJdbcUrl - Update WebLogic Datasource Connection String
Enter Your Choice : updateDSPassword
Enter the full path of Applications Context File [DEFAULT -]:
Enter weblogic admin server password:
Enter the APPS user password:
Note: We can also update the apps password in EBS Datasource from Weblogic console.
What if we missed to run adpreclone.pl and copy completed?
What will happened to scheduled request if submitted used end dated?
How to monitor CM with OEM?
What do do if patch fails in relinking?
what is batch size in adpatch?
While applying a patch, the patch may contain scripts that update data in batches. This prompt allows you to specify how many rows will be updated at a time. It is recommended that you accept the default unless you know your system well.If you enter a negative or an invalid number, adpatch will use the default value (in this case a value of 1000).
(Or)
The Batch Size refers to the number of rows to commit at a time when cerrtain scripts run.If we dno't enter a specific value, Auto Patch takes the default, Which is normall set to a relatively smaller value to accommodate systems with a small rollback segment.
+++++++++++++Doc ID 1311402.1+++++++++++
What is the difference between CPU patch and PSU patch.Can we apply CPU patch over psu patch?
patch conflict
Different types of Patch Conflicts - Superset/Subset/Duplicate/Bug/File [ID 563656.1]
Patch:
Patch is a piece of software or code designed to fix the problems of the existing software. These fixes can be security vulnerability fixes or bug fixes.In certain cases the patches increases or enhances the functionality of the software as well
Interim patch:
Interim patches are also known as “one of patches”. Interim patches are released in between the release of CPU or PSU patch to fix a specific issue. These patches generally address specific bug’s fixes for a specific customer and should not be applied unless specified by Oracle support services.
Critical patch update (CPU):
These are the cumulative patches consisting of security fixes.
Patch set update (PSU):
These are quarterly cumulative patches that contain security fixes as well as additional fixes sometimes, PSU includes feature enhancements as well. So we can say that PSU includes the security fixes of the CPU plus additional fixes.PSUs contain CPUs plus other bug fixes. Which path your choose (CPUs only or PSUs only) depends on your requirements and needs. Systems that store sensitive information and/or are exposed to the internet (or are more susceptible to attack) will need to go the CPU route (where patches are released on a quarterly basis).
Once the PSU is applied only PSUs can be applied in future quarters until the database is upgraded to knew base version
Can we apply CPU patch over psu patch?
Depend.
But OPATCH ultilty handle with confilct of patchset installed.
Patch Conflicts
All patches may not be compatible with one another. For example, if a patch has been applied, all the bugs fixed by that patch could reappear after another patch is applied. This is called a conflict situation. OPatch detects such situations and raises an error when a it detects a conflict.
http://docs.oracle.com/cd/B19306_01/em.102/b16227/oui8_opatch.htm
Logo change in EBS:
https://www.funoracleapps.com/2023/01/how-to-changeadd-custom-logo-at-top-of.html
What are the high level steps for upgrading 11i to R12.1.3?
Restart the failed patch from place you left
adop phase=apply patches=patch restart=yes
Restart the failed patch from beginning
adop phase=apply patches=patch abandon=yes
Ignore Failed Patch and Apply New Patch
adop phase=apply patches=NewPatch abandon=yes
Reapply a previously applied Patch
adop phase=apply options=forceapply patches=patch
How to abort a patching cycle
adop phase=abort
Post step run below command
adop phase=cleanup cleanup_mode=full
adop phase=fs_clone
If the system crashes and unable to proceed the patch then what to do
We will see error as
Error: Unable to continue as already another user is using adzdoptl.pl.
Previous session exist, cannot continue as per user input
Steps:
➢ Run the following statement to find out the session that is in running state:
select adop_session_id from ad_adop_sessions where status='R';
➢ Set the status to Completed 'C' for that session to re- try the phase that was interrupted
update ad_adop_sessions set status='C' where status='R’
ADOP question : https://www.funoracleapps.com/2021/11/ebs-122-adop-interview-questions.html
Oracle Database keeps records of actions of transactions, before they are committed and Oracle needs this information to rollback or undo the changes to the database. These records are called rollback or undo records.
These records are used to:
Rollback transactions - when a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction.
Recover the database - during database recovery, undo records are used to undo any uncommitted changes applied from the redolog to the datafiles.
Provide read consistency - undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.
UNDO_RETENTION:
This value specifies the amount of time, undo is kept in the tablespace.The parameter undo_retention to set the amount of time you want undo information retained in the database.
The default value for the UNDO_RETENTION parameter is 900.
If an active transaction requires undo space and the undo tablespace does not have available space, then the system starts reusing unexpired undo space (if retention is not guaranteed). This action can potentially cause some queries to fail with the ORA-01555 "snapshot too old" error message.
UNDO_MANAGEMENT = AUTO
