Get Instant Database Support Featured Articles I am a title 01 I am a title 01 I am a title 01 View all Performance Tuning Articles Tune your Oracle database for high performance and low latency I am a title 01 I am a title 02 I am a title 03 View all Oracle Data Guard Articles Keep you primary in sync with standby always I am a title 01 I am a title 02 I am a title 03 View all Oracle RAC Articles Oracle RAC enables high availability and scalability with seamless failover across multiple servers. I am a title 01 I am a title 02 I am a title 03 View all Oracle Golden Gate Articles Oracle GoldenGate ensures real-time data integration and replication for heterogeneous systems I am a title 01 I am a title 02 I am a title 03 View all Stay Ahead in the World of Database and Cloud Script to create JUSTLEE schema in Oracle Connect Linux Instance with SSH Keys SQL Project - Library Management SQL Project - Supermarket Billing Linux Project - Monitor Server Disk Space Install Oracle 19c with ASM Oracle 21c Installation on Linux Move Spfile to ASM Oracle Database 19c RPM-based Installation On Oracle Linux 7 MySql 8.0 on OEL 7.9 PostgreSQL15 on CentOS 7 MySQL 8.0 on CentOS 7

Oracle 23ai Installation on Linux In this article, we will look at installation and configuration of oracle 23ai on Oracle Linux 8. The steps are same for Oracle Linux 9. We will be using Oracle Database 23ai RPM for this activity Oracle 23ai Pre- requisites Installing and configuration Oracle 23ai Currently Oracle 23ai is only available as RPM package for installation Oracle 23ai Pre-requisites Install the 23ai preinstall package using dnf utility. This package will perform all the pre-requisites on your Linux server dnf install -y oracle-database-preinstall-23ai Download the Oracle 23ai rpm package using wget Wget https://download.oracle.com/otn-pub/otn_software/db-free/oracle-database-free-23ai-1.0-1.el8.x86_64.rpm Install Oracle 23ai As root user, install the 23ai rpm package using the dnf utility dnf install -y oracle-database-free-23ai-1.0-1.el8.x86_64.rpm Configure FREE database with FREE PDB and creating sys and system user password /etc/init.d/oracle-free-23ai configure You can set your own password for the SYS and the SYSTEM user when prompted. Switch to Oracle user and setup the bash profile su - oracle vi .bash_profile Add ORACLE_SID, ORACLE_BASE, ORACLE_HOME and update the PATH variable # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs export ORACLE_SID=FREE export ORACLE_BASE=/opt/oracle export ORACLE_HOME=/opt/oracle/product/23ai/dbhomeFree PATH=$PATH:$HOME/.local/bin:$ORACLE_HOME/bin export PATH Export bash profile . .bash_profile Connect to sqlplus and start the Oracle 23ai database sqlplus / as sysdba SQL> startup SQL> select name,open_mode from v$containers; Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

Oracle 23ai

Oracle Data Pump

Check FRA location utilization Fast Recovery Area must be monitored regularly. Sometimes FRA runs our of space and a DBA must be able to gather FRA space utilization. It is very important to monitor space usage in the fast recovery area to ensure that it is large enough to contain backups and other recovery-related files. Below script gives you Fast Recovery Area utilization details set linesize 500 col NAME for a50 select name, ROUND(SPACE_LIMIT/1024/1024/1024,2) "Allocated Space(GB)", round(SPACE_USED/1024/1024/1024,2) "Used Space(GB)", round(SPACE_RECLAIMABLE/1024/1024/1024,2) "SPACE_RECLAIMABLE (GB)" , (select round(ESTIMATED_FLASHBACK_SIZE/1024/1024/1024,2) from V$FLASHBACK_DATABASE_LOG) "Estimated Space (GB)" from V$RECOVERY_FILE_DEST; Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

Data Block

Oracle Data Guard Manual Switchover and Failover on Physical Standby Seamlessly transitioning between primary and standby roles, allowing database administrators to efficiently manage high-availability operations and disaster recovery scenarios in an Oracle Data Guard environment . Perform Manual Switchover on Physical Standby Performing Manual Failover on Physical Standby Perform Manual Switchover on Physical Standby Connect to proddb database via client and keep querying below sqlplus sys/sys@proddb as sysdba select name, open_mode, db_unique_name, database_role from v$database; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE --------- -------------- ---------------------- -------------- proddb READ WRITE proddb PRIMARY Check primary and standby for any gaps On primary: =========== select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2; On standby: =========== select NAME, VALUE, DATUM_TIME from V$DATAGUARD_STATS; We will first convert primary to standby and later standby to primary On primary: =========== select SWITCHOVER_STATUS from V$DATABASE; You must see TO STANDBY or SESSIONS ACTIVE alter database commit to switchover to physical standby with session shutdown; startup mount; At this stage, there is no primary to accept queries from client. Run below query on client putty terminal. The query will hang and wait until standby is converted to primary select name, open_mode, db_unique_name, database_role from v$database; Convert standby to primary: Our primary is already converted to standby. Now it’s time to convert original standby into primary select SWITCHOVER_STATUS from V$DATABASE; alter database commit to switchover to primary with session shutdown; alter database open; At this stage, the client query would execute successfully! On new standby – Initially your primary database: Start MRP alter database recover managed standby database disconnect; Revert back: Once again follow the above process from top and re-execute steps in proper databases to revert back to original setup. Performing Manual Failover on Physical Standby Failover is when your primary database is completely lost. When there is a failover, standby is converted into primary but primary is not converted into standby as it is lost. If you do not have Flashback enabled on primary, you must re-create primary from scratch (Using RMAN duplicate method). In this example, we have already enabled flashback on both primary and standby. Crash Primary Database Perform Failover to Standby Rebuild Primary After Failover Our current physical standby server overview ​Database SID Database Role Proddb Primary Database proddb_st Physical Standby Crash Primary database Let’s crash primary (proddb): In order to simulate failure, we will shut down the primary server proddb. As root user, shutdown the server without shutting down DB. Execute query on client: At this stage, there is no primary to accept queries from client. Run below query on client putty terminal. The query will hang and wait until standby is converted to primary select name , open_mode , db_unique_name , database_role from v$database ; Perform Failover to Standby Minimize data loss (proddb): If you can mount the primary database, then flush the logs to standby On primary : =========== SQL > startup mount SQL > alter system flush redo to 'proddb_st' ; If you are not able to mount the database, then check if primary server is up. In that case manually copy archive logs from primary to standby and register those logs on standby database On standby : =========== SQL > alter database register physical logfile '&logfile_path' ; Check for redo gaps: If any gap exists, copy log files from primary and register on standby as per last step On standby : =========== SQL > select THREAD# , LOW_SEQUENCE# , HIGH_SEQUENCE# from V$ARCHIVE_GAP ; Start failover: We need to activate standby so that client can continue to access even after failover On standby : =========== SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL ; SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH ; SQL > select SWITCHOVER_STATUS from V$DATABASE ; You must see TO PRIMARY or SESSIONS ACTIVE. Switch standby to primary SQL > alter database commit to switchover to primary with session shutdown ; SQL > alter database open ; Check client query: Check the query you executed in step 2 on client, it must get executed Rebuild Primary After Failover Post failover, there are two methods of rebuilding your failed primary Method 1: Rebuild from scratch à RMAN duplicate Method 2: Flashback database à only if Flashback was enabled Note: In our earlier activity, we have performed Failover. Current state of your servers should be ​Database SID ​Database Role ​proddb ​crashed (we shutdown the server) proddb_st ​Primary Get the SCN at which standby became primary: We need to get the SCN at which the current primary(proddb_st) was activated. This SCN will be used to flashback crashed (proddb) database SQL > select to_char ( standby_became_primary_scn ) from v$database ; Flashback crashed primary(proddb): Start the proddb server, mount the database and flashback proddb to SCN from the last step SQL > startup mount ; SQL > flashback database to scn < standby_became_primary_scn > ; Convert crashed primary to physical standby(proddb): Now the old primary is at SCN when proddb_st was activated. We can convert proddb into a physical standby and start redo apply SQL > alter database convert to physical standby ; SQL > alter database recover managed standby database disconnect ; Current state of your databases should be ​Database SID Database Role proddb Physical Standby proddb_st Primary Revert to original configuration: At this stage, if you would like to revert the current state of databases to original, you can perform manual switchover! Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

AWS Mount EFS on EC2 Instance You might have created an Elastic File System (EFS) and now would like to mount it on EC2 instance. Get EFS link Mount EFS on EC2 Add to... Oracle Database Migration to AWS Cloud Find different ways to migrate an Oracle database from on-premises to AWS cloud using different tools available for migration. You could... Convert pem to ppk file Using PuTTygen When you create an EC2 instance, aws provides you with .pem file. This .pem file is used to connect EC2 instance from a Linux host. In...

Oracle Locks

SQL Case Statement in Oracle Oracle SQL CASE statement gives you the flexibility to use sql IF ELSE logic in a SELECT, WHERE and JOIN clause. Note: same CASE statement is used in PL/SQL blocks. Select CASE SQL For example, let us assume we need to count the number of employees whose salary is less than 2000, between 2000 & 4000 and above 4000. The below query helps counting such distinct values from a single column itself SELECT COUNT (CASE WHEN salary < 2000 THEN 1 ELSE null END) count1, COUNT (CASE WHEN salary BETWEEN 2001 AND 4000 THEN 1 ELSE null END) count2, COUNT (CASE WHEN salary > 4000 THEN 1 ELSE null END) count3 FROM employees; COUNT1 COUNT2 COUNT3 ---------- ---------- ---------- 0 43 64 With the output we can see that There are 0 employees whose salary is less than 2000 There are 43 employees whose salary is above 2000 and below 4000 There are 64 employees whose salary is grater than 4000 SQL Select CASE Example 1 For each customer in the sample customers table, the following sql query lists the credit limit as Low if it equals $100, High if it equals $5000, and Medium if it equals anything else SELECT cust_last_name, CASE credit_limit WHEN 100 THEN 'Low' WHEN 5000 THEN 'High' ELSE 'Medium' END FROM customers; CUST_LAST_NAME CASECR -------------------- ------ ... Bogart Medium Nolte Medium Loren Medium Gueney Medium SQL Select CASE Example 2 We would like to output the name of the department for the corresponding department number. For all the departments from 10 to 40 have their department name values listed, the ELSE is used to deal with any other depart number other than listed (10 to 40) SELECT ename, empno, deptno (CASE deptno WHEN 10 THEN 'Accounting' WHEN 20 THEN 'Research' WHEN 30 THEN 'Sales' WHEN 40 THEN 'Operations' ELSE 'Unknown' END) department FROM emp ORDER BY ename; SQL Select CASE Example 3 The following sql query finds the average salary of the employees in the sample table employees, using $2000 as the lowest salary possible SELECT AVG(CASE WHEN e.salary > 2000 THEN e.salary ELSE 2000 END) "Average Salary" FROM employees e; Average Salary -------------- 6461.68224 Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

Find last CPU applied in oracle Critical Patch Updates are sets of patches containing fixes for security flaws in Oracle products. The Critical Patch Update program (CPU) was introduced in January 2005 to provide security fixes on a fixed, publicly available schedule to help customers lower their security management costs. Generally if you have one single database install then checking the database inventory will give you the latest patch details. But! if we have multiple database in single oracle home then it might not give correct results. There might be a chance that one DB is applied with latest patches and others are not. In such cases, we need to check last CPU applied by logging into the database using below query Query to Check Last CPU Applied col VERSION for a15; col COMMENTS for a50; col ACTION for a10; set lines 500; select ACTION,VERSION,COMMENTS,BUNDLE_SERIES from registry$history; Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

Find SQL execution plan change If you would like to find out change in SQL plan of a query, below script will help you find the SQL plan ID for previous executions and check if there is any change in SQL plan ID. set pagesize 1000 set linesize 200 column begin_interval_time format a20 column milliseconds_per_execution format 999999990.999 column rows_per_execution format 999999990.9 column buffer_gets_per_execution format 999999990.9 column disk_reads_per_execution format 999999990.9 break on begin_interval_time skip 1 SELECT to_char(s.begin_interval_time,'mm/dd hh24:mi') AS begin_interval_time, ss.plan_hash_value, ss.executions_delta, CASE WHEN ss.executions_delta > 0 THEN ss.elapsed_time_delta/ss.executions_delta/1000 ELSE ss.elapsed_time_delta END AS milliseconds_per_execution, CASE WHEN ss.executions_delta > 0 THEN ss.rows_processed_delta/ss.executions_delta ELSE ss.rows_processed_delta END AS rows_per_execution, CASE WHEN ss.executions_delta > 0 THEN ss.buffer_gets_delta/ss.executions_delta ELSE ss.buffer_gets_delta END AS buffer_gets_per_execution, CASE WHEN ss.executions_delta > 0 THEN ss.disk_reads_delta/ss.executions_delta ELSE ss.disk_reads_delta END AS disk_reads_per_execution FROM wrh$_sqlstat ss INNER JOIN wrm$_snapshot s ON s.snap_id = ss.snap_id WHERE ss.sql_id = '&sql_id' AND ss.buffer_gets_delta > 0 ORDER BY s.snap_id, ss.plan_hash_value; Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.