foalabs

Useful RMAN Queries Query for Currently running RMAN backups like Full, Incremental & Archivelog backups col STATUS format a9 col hrs format 999.99 select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,'mm/dd/yy hh24:mi') start_time, to_char(END_TIME,'mm/dd/yy hh24:mi') end_time, elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS order by session_key; Query for RMAN Total pending and completed work SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,  ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK; Query to show the progress size in MB of RMAN backup/recovery select to_char(start_time, 'dd-mon-yyyy@hh24:mi:ss') "Date",  status,  operation, mbytes_processed from v$rman_status vs where start_time > sysdate -1 order by start_time; Query to check historical RMAN Backup Status set linesize 500 ...

Oracle EBS R12 SQL Queries For Ledgers And Legal Entities 1.  GL_LEDGERS GL_LEDGERS stores information about the ledgers defined in the Accounting Setup Manager and the ledger sets defined in the Ledger Set form. Each row includes the ledger or ledger set name, short name, description, ledger currency, calendar, period type, chart of accounts, and other information. Some columns in this table are not applicable for ledger sets. In this case, default values would be inserted into these columns for ledger sets. 2.  GL_LEDGER_CONFIG_DETAILS GL_LEDGER_CONFIG_DETAILS stores information about a primary ledger, its related secondary ledgers and legal entities, and the state of each of their setup steps. The object type specifies the type of object stored in this row: primary ledger, secondary ledger, or legal entity. The object id and object name give the defining column and name of the object. Valid steps for objects include setup of ledgers, definition of reporting currencies, assi...

How to change/add the custom logo at the top of the login screen in EBS 12.0/12.1/12.2 Step 1: Change Profile Value to the Image file name Login to Applications as System Administrator. Select: System Administrator > Profile - System. Query profile "Corporate Branding Image for Oracle Applications". Change the SITE level value to the name of the custom image file (e.g. my_company_logo.gif). Save the change. Step 2: Copy the Image on All application servers Open a PUTTY session to the Application server and source Applications environment. Navigate to the $OA_MEDIA directory. Copy the custom image file to this directory and make sure the image size is 155*20 pixels and permissions are set properly. Now clear the browser cache and access the environment. Now the new logo should be rendered. If you like please follow and comment

How to Change TimeZone on OCI Compute and DBCS systems I was planning to change time zone on my OCI computer servers and the DBCS system. So thought lets share the steps to do the same. Steps to Change Compute Instance Server TimeZone 1) Make sure all services are stopped. 2) Make sure you are logged in as root. 3) Run command to check the timezones available  timedatectl list-timezones Asia/Kolkata Make a note of the required timezone needed. 4) Change timezone accordingly using below command timedatectl set-timezone Asia/Kolkata 5) Reboot Server. Steps to Change DBCS Server TimeZone 1) Login to DBCS server. 2) Stop the database and other services. 3) Run command to check the timezones available  timedatectl list-timezones Asia/Kolkata Make a note of the required timezone needed. 4) Change timezone accordingly using below command timedatectl set-timezone Asia/Kolkata 5) Switch to grid user. Verify TZ entry in the file  is located in GRID_HOME/crs/install/s_crsconfig...

Check Fragmentation in Oracle Database Check tables in Schema having fragmented space. Enter schema name in which you find top 50 fragmented tables. select * from ( select owner,table_name,round((blocks*8),2) "size (kb)" , round((num_rows*avg_row_len/1024),2) "actual_data (kb)", (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)", round((((round((blocks * 8), 2) - round((num_rows * avg_row_len / 1024), 2)) / round((blocks * 8), 2)) * 100 - 10 ),2) "reclaimable space % " from dba_tables where owner in ('&SCHEMA_NAME' ) and (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2)) order by 5 desc ) where rownum < 50; Check in percentage of table fragmentation select table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE", round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE", round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||...

EBS R12.1: PA: Transaction Import WebADI Document Missing Columns Error: The Transaction Import Web ADI layout has missing Project Number and Expenditure Item Date columns. Solution: Rebuild the ldt using FNDLOAD. 1) Apply the FNDLOAD command, FNDLOAD apps/<password> 0 Y UPLOAD $BNE_TOP/patch/115/import/bneintegrator.lct $PA_TOP/patch/115/import/US/pabneint.ldt 2) Retest the issue. If you like please follow and comment

DCS-10001:Internal error encountered Oracle Database Appliance While changing the ODA admin password getting error as  "DCS-10001:Internal error encountered:" Solution: Restart DCS agent and zookeeper on both nodes systemctl stop initdcsagent /opt/zookeeper/bin/zkServer.sh stop /opt/zookeeper/bin/zkServer.sh start systemctl start initdcsagent If you like please follow and comment

No supported authentication methods available While accessing linux putty receiving error as no supported authentication. Steps: Edit /etc/ssh/sshd_config configuration file using vi editor. [root@server ~]# vi /etc/ssh/sshd_config Check and replace with yes for below entry PasswordAuthentication no change it to PasswordAuthentication yes save the file Than restart the ssh service using below command. systemctl restart sshd Restart the network service using below command. systemctl restart network Lets Try to access the server again If you like please follow and comment

How to Disable or Suppress OEM Alerts for Alert Log ORA Errors in OEM 12c and 13c I wanted the following errors in the 12c DB alert log are expected and can be ignored. ORA-700 kskvmstatact: excessive swapping observed ORA 7445 [_IO_default_xsputn] But if we enable Generic Alert Log Error Metric, agent will collect this error from alert log and generate an alert. Modifying the Metric thresholds from EM Console does not make any difference and users will continue to get these alerts. By default we can not exclude ORA-700 alerts. Bug 19495842 - CHANGE THRESHOLD IN THE SWAP WARNING IN THE ALERT_<SID>.LOG Steps: 1. Stop the agent that is monitoring the database $/AGENT_INST/bin/emctl stop agent 2. Modify the entry in the <AGENT_INST>/sysman/config/emd.properties file from: adrAlertLogErrorCodeExcludeRegex=.*700.* This should filter all the ORA-700 errors. If the requirement is to filter only the "(ORA 700 [kskvmstatact: excessive swapping observed])" error, you can se...

How To Disable SSH Server Weak Key Exchange Algorithm diffie-hellman-group1-sha1 in Oracle Linux The  diffie-hellman-group1-sha1  key exchange algorithm is considered a weaker algorithm. OpenSSH on Oracle Linux 7 currently supports and enables the algorithm that security/vulnerability scanners such as Qualys may detect as vulnerable. To ensure optimal security, one should consider disabling weaker OpenSSH key exchange algorithms. This document describes how to disable the  diffie-hellman-group1-sha1  key exchange algorithm within on Oracle Linux 7. The same process may also be used to disable other algorithms. Steps to disable Oracle Linux 7 OpenSSH  diffie-hellman-group1-sha 1 key exchange algorithm 1. Check whether key exchange algorithm  diffie-hellman-group1-sha1  is currently enabled: # sshd -T | egrep -i ^kexalgorithms | grep diffie-hellman-group1-sha1; echo $? or # nmap --script ssh2-enum-algos -sV -p 22 127.0.0.1 | grep diffie-hellman-group1-s...

How to Sync the Physical Standby Gaps Using RECOVER FROM SERVICE in Oracle Oracle Introduced new commands RECOVER FROM SERVICE for make the database sync if dataguard out of sync by reducing the steps. Command “RECOVER … FROM SERVICE” below steps done automatically 1. Creating the incremental back of RMAN from Primary site of the SCN at standby DB. 2. Transfer the incremental backup at Standby 3. Apply the incremental backup at standby to make them sync. This will help incase even if the archives are deleted on the primary database as well.  Steps: 1. Identify the gap between both Primary and Standby Site: Run the following command on both primary and standby DB to identified the out of sync. -- On Standby and primary both -- Check SCN difference of DB SELECT CURRENT_SCN FROM V$DATABASE; --Check SCN difference of Files select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh; 2) On Standby DB: Stop the replication service MRP on Standby Database: -- On Standby ALTER DATABASE REC...

How to take RMAN Backup on DBCS in OCI to Object Storage We are going to learn how we can take a RMAN backup on DBCS and store it in Object storage bucket in OCI Source System Information: DB version: 11.2.0.4 DB on OCI DBCS Type of Database : Oracle EBS Single Node Steps: 1) Create an OCI Private bucket to store RMAN backup. Click on Navigation Menu > Object Storage > Object Storage > Buckets   2) As My OCI region is singapore so my  Object_Storage URL:   https://objectstorage.ap-singapore-1.oraclecloud.com Bucket Name: PROD_RMAN_BACKUP 3) Connect to DBCS System Database Node login as: opc sudo su - oracle 4) Change to the directory that contains the backup module  opc_install.jar  file. cd /opt/oracle/oak/pkgrepos/oss/odbcs In case you don’t have file you can download on on-prem from below link. URL  https://www.oracle.com/database/technologies/oracle-cloud-backup-downloads.html Also make sure java is installed on java. The version need to be cor...

Reset Root Password In Linux 6 In post I am sharing how to reset root password if you forget. Reset Forgotten Root Password By Booting into Single User Mode Reset Forgotten Root Password By Booting into Rescue Mode Way 1: Reset Root Password by Booting into Single  User Mode a) Go to Grub menu by  Reboot your system and interrupt in boot screen by using any key from your keyboard  b) In Grub menu press 'a' to modify kernel parameters before booting. c) Append S or single or 1 after a space at the end of the line and press Enter key to boot into single user mode. d) Reboot the system using init 6 or reboot command e) Now we can successfully login. Way 2: Reset Root Password by Booting into Rescue Mode using ISO image a)  Insert the bootable media through USB or DVD drive which is compatible for you and reboot your system. It will take to you to the below screen. Hit  Rescue installed system  to launch the  Rescue  mode b) Proceed Further based...