Search Results

Linux SQL Oracle Oracle Database Oracle ASM Oracle RAC Oracle Golden Gate Enterprise Manager MySQL PostgreSQL AWS Home Temp Tablespace Utilization Monitor and manage temporary tablespace usage in Oracle. One of the frequent errors in a transactional database is ORA-1652: unable to extend temp segment . On a high level, temp tablespace is used for sorting purpose and it is shared tablespace among different users in the database. Check Temp Size Temp % Utilization Top 10 Sessions Using High Temp Find Current Sessions Using Temp Check Temp Size This below query will work for Oracle 12c version and above only SELECT * FROM DBA_TEMP_FREE_SPACE; TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE SHARED INST_ID ------------------ --------------- --------------- ---------- ------------- ---------- TEMP 137363456 137363456 134217728 SHARED Below query will work in any version of Oracle database to check temp tablespace utilization set lines 200 select TABLESPACE_NAME, sum(BYTES_USED/1024/1024),sum(BYTES_FREE/1024/1024) from V$TEMP_SPACE_HEADER group by TABLESPACE_NAME; Temp % Utilization Query to check percentage (%) utilization of temp tablespace select (s.tot_used_blocks/f.total_blocks)*100 as "percent used" from (select sum(used_blocks) tot_used_blocks from v$sort_segment where tablespace_name='TEMP') s, (select sum(blocks) total_blocks from dba_temp_files where tablespace_name='TEMP') f; Top 10 Sessions Using High Temp Finding the top 10 sessions with highest temp tablespace usage cursor bigtemp_sids is select * from ( select s.sid, s.status, s.sql_hash_value sesshash, u.SQLHASH sorthash, s.username, u.tablespace, sum(u.blocks*p.value/1024/1024) mbused , sum(u.extents) noexts, nvl(s.module,s.program) proginfo, floor(last_call_et/3600)||':'|| floor(mod(last_call_et,3600)/60)||':'|| mod(mod(last_call_et,3600),60) lastcallet from v$sort_usage u, v$session s, v$parameter p where u.session_addr = s.saddr and p.name = 'db_block_size' group by s.sid,s.status,s.sql_hash_value,u.sqlhash,s.username,u.tablespace, nvl(s.module,s.program), floor(last_call_et/3600)||':'|| floor(mod(last_call_et,3600)/60)||':'|| mod(mod(last_call_et,3600),60) order by 7 desc,3) where rownum < 11; Find Current Sessions Using Temp Below is the query to identify the current users who are right now using TEMP tablespace OR utilizing the TEMP segments SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_used, c.sql_text FROM v$session a, v$tempseg_usage b, v$sqlarea c, (select block_size from dba_tablespaces where tablespace_name='TEMP') d WHERE b.tablespace = 'TEMP' and a.saddr = b.session_addr AND c.address= a.sql_address AND c.hash_value = a.sql_hash_value AND (b.blocks*d.block_size)/1048576 > 1024 ORDER BY b.tablespace, 6 desc; Further Read Tablespace Administration Become a top notch DBA. Unlimited Courses & Xperiments! Browse Courses

Linux SQL Oracle Oracle Database Oracle ASM Oracle RAC Oracle Golden Gate Enterprise Manager MySQL PostgreSQL AWS Home Find Invalid Objects Inside Oracle Identify and resolve invalid database objects in Oracle. Changing things in database can cause some objects to become INVALID . Query to check invalid objects in oracle SELECT OWNER, OBJECT_TYPE, OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS != ‘VALID’ ORDER BY OWNER, OBJECT_TYPE; OR SELECT OWNER, STATUS FROM DBA_OBJECTS WEHRE STATUS !=’VALID’ GROUP BY OWNER, STATUS; Recompiling Invalid Objects You can handle INVALID objects to make them VALID. Execute the below script EXEC UTL_RECOMP.recomp_serial(‘schema name’); --> Oracle 9i EXEC DBMS_UTILITY.COMPILE_SCHEMA(‘SCOTT’); --> 10g and above Note: Oracle highly recommends running the script towards the end of any migration/upgrade/downgrade. Oracle invalid objects sometimes have dependencies, so it may be necessary to run the oracle invalid objects recompile repeatedly. Become a top notch DBA. Unlimited Courses & Xperiments! Browse Courses

Interview Questions Oracle DBA Interview Questions & Answers Explain about data and how do you store data? Data is any value which we store for future reference. There are different types of data... Senior Oracle DBA Interview Questions Put your learnings to test by trying to answer below Senior Oracle DBA Interview Questions. I am sure these will definitely challenge... Oracle Golden Gate Interview Questions Here are some of the frequently asked Oracle Golden Gate interview questions and answers. Enjoy! Q. What is the significance of Oracle... Oracle SQL Interview Questions Here are some of the frequently asked SQL interview questions. Enjoy! Q1. What is the difference between VARCHAR & VVARCHAR2? Both... Oracle 12c Multi-tenant Interview Questions Interview questions specific to Oracle 12c Multi-tenant architecture. Enjoy! Q. What are the major changes in architecture for 12c? From...

Linux SQL Oracle Oracle Database Oracle ASM Oracle RAC Oracle Golden Gate Enterprise Manager MySQL PostgreSQL AWS Home SQL Case Statement in Oracle Simplify conditional logic with the 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 Become a top notch DBA. Unlimited Courses & Xperiments! Browse Courses

Linux SQL Oracle Oracle Database Oracle ASM Oracle RAC Oracle Golden Gate Enterprise Manager MySQL PostgreSQL AWS Home Oracle 12c Multi-tenant Interview Questions Master multi-tenant architecture with these Oracle 12c interview questions. Interview questions specific to Oracle 12c Multi-tenant architecture. Enjoy! Q. What are the major changes in architecture for 12c? From 12c Onwards, the instance is shared with multiple databases. This multiple databases are self contained and pluggable from one database to another database. This is very useful methodology where database consolidation. In short a single SGA and background process will be shared to multiple databases, the databases can be created on fly and drop or attach and detach from one server to another server. Q. What is a pluggable database (PDB) in Multitenant Architecture? Pluggable Databases (PDBs) is new in Oracle Database 12c Release 1 (12.1). You can have many pluggable databases inside a single Oracle Database occurrence. Pluggable Databases are always part of a Container Database (CDB) but a PDB looks like a normal standalone database to the outside world. Q. Does In-memory require a separate license cost? Yes, In-memory does require a separate license cost. Q. Why would I consider using the Multitenant option? You should consider following Database Consolidation Goals to achieve: Reduce Total Cost of Operation Administration costs Operations costs Data center costs Storage costs Contingency costs Improve Functionalities Resource utilization Manageability Integration Service management Must not need to change applications Must not harm performance Must provide resource management and isolation between applications Must simplify patching and upgrade of Oracle Database Q. What are the common concepts of multitenant database? Multitenant database consists of CDB is a container database which is similar like standalone database. Called CDB$ROOT PDB$SEED is a template database to create a databases within the CDB databases PDB are individual or application databases Data dictionary between this databases are shared via internal links called object link and data link Users between CDB and PDB are different, there will be common users (starts with C##) and local users When the CDB starts up, the PDB will be in mount state, you must open them exclusively Q. Will CDB’s become the future? Yes, Oracle has already depreciated standalone database development. No further improvements will be released in future for standalone databases. Q. How many PDBs can you create? 12cR1 –> 252 PDBs 12cR2 –> 4096 PDBs Q. Can multiple CDBs run on the same server? Yes! Q. Can multiple CDBs run out of the same ORACLE_HOME installation? Yes, you can invoke DBCA and create new CDBs out of same Oracle_Home Q. What are the methods to create Multitenant Database? DBCA method DBCA silent method Manual method using CREATE DATABASE statement Q. What is the limit of container databases (CDBs) on a server? As many as supported by server CPU, RAM and Hard disk space. Q. How do I know if my database is Multitenant or not? You can use below query to identify a CDB database: SELECT NAME, OPEN_MODE, CDB FROM V$DATABASE; SHOW CON_ID; SHOW CON_NAME; Q. How to distinguish you are in CDB or PDB? Once you logged in you can check show con_name or con_id will show you which db you are in. Q. What are the different ways you can create a PDB? Copying from PDB$SEED Copying from another PDB Copying from a remote PDB Converting a Non-CDB into PDB Unplugging and plugging in a PDB Q. Can I have one PDB at release 1, and a second PDB at release 2? No, one instance, one version for all PDBs. Q. What Pluggable databases do we have in this container database ? You can check this by querying v$containers : SELECT NAME, OPEN_MODE FROM V$CONTAINERS; SHOW pdbs; Q. How do you switch from one container to another container inside SQL*PLUS? ALTER SESSION SET CONTAINER=pdb1; Q. How about the datafiles system, sysaux , undo, redo etc , does they create when you create PDB? Datafiles are individual to each database for cdb and each pdb Undofiles and redofiles are only one across container From 12cR2 onwards we can create local undo for each PDB Tempfiles can be created in each database or share one across all databases SGA is shared across all databases Background process are shared across all databases , no additional back ground process defined Q. Is the alert log the same for all pdbs in a cdb, or are they different? Yes, one CDB, one alert log. Q. How can I connect to a PDB directly from SQL* PLUS? You can use Oracle easy connect method to connect a PDB directly. CONNECT username/password@host[:port][/service_name][:server][/instance_name] OR sqlplus user/password@//localhost/pdb2 Q. How do I switch to main container Database? ALTER SESSION SET CONTAINER = CDB$ROOT; Q. As you said, if SGA and background process are shared, is there any performance impact? Ideally this architecture is used for database consolidation projects which and where small databases are shared in a single database host and not that high critical applications running. This leverages the reduction in licensing cost and also resource utilization effectively. Q. How do I start up a Pluggable database? From CDB$ROOT container ALTER PLUGGABLE DATABASE PDB1 OPEN; Q. How about creating a user? Normally you will use create user username identified by password, however this is not work anymore. When you want to create a common user across all databases for example, you must use C## as prefix Create user c##username identified by password; will create common user across all databases Create user c##username identified by password container=current; will create common user only at current container create user username identified by password container=all; does not work since the username does not contain c## prefix Q. How about AWR data, does it common across all databases or individual to database? That is why you have individual sysaux tablespace for each database, whenever the AWR statistics collected the statistics will be pushed respective databases not the common sysaux. Since this will give you the ability to have self contained database where if you plug this database to another instance, the statistics will not lost. Q. Which parameters are modifiable at PDB level? select NAME, ISPDB_MODIFIABLE from V$PARAMETER; Q. What is the difference between Container ID Zero and One? CON_ID “0” means data does not pertain to any particular Container but to the CDB as a whole. For example, a row returned by fetching from V$DATABASE pertains to the CDB and not to any particular Container, so CON_ID is set to “0”. A CONTAINER_DATA object can conceivably return data pertaining to various Containers (including the Root which has CON_ID==1) as well as to the CDB as a whole, and CON_ID in the row for the CDB will be set to 0. Following table describes various values of CON_ID Column in Container Data Objects. 0 = The data pertains to the entire CDB 1 = The data pertains to the root 2 = The data pertains to the seed 3 – 254 = The data pertains to a PDB, Each PDB has its own container ID. Q. Are there any background processes ex, PMON, SMON etc associated with PDBs? No. There is one set of background processes shared by the root and all PDBs. Q. Are there separate control file required for each PDB? No. There is a single redo log and a single control file for an entire CDB. Q. Are there separate Redo log file required for each PDB? No. There is a single redo log and a single control file for an entire CDB. Q. Can I monitor SGA usage on a PDB by PDB basis? SQL> alter session set container=CDB$ROOT; SQL> select POOL, NAME, BYTES from V$SGASTAT where CON_ID = '&con_id'; SQL> select CON_ID, POOL, sum(bytes) from v$sgastat group by CON_ID, POOL order by CON_ID, POOL; Q. Do I need separate SYSTEM and SYSAUX tablespaces for each of my PDB? There is a separate SYSTEM and SYSAUX tablespace for the root and for each PDB. Q. Where is user data stored in CDB? In a CDB, most user data is in the PDBs. The root contains no user data or minimal user data. Q. How can I create a pluggable database ? sql> create pluggable database x admin user a identified by p; Q. How to drop a PDB irrevocably? sql> drop pluggable database x including datafiles; Become a top notch DBA. Unlimited Courses & Xperiments! Browse Courses

Linux SQL Oracle Oracle Database Oracle ASM Oracle RAC Oracle Golden Gate Enterprise Manager MySQL PostgreSQL AWS Home Oracle 19c Installation on Linux Install Oracle 19c on Linux with this comprehensive guide. Oracle 19c adds more features to Oracle database and this article demonstrates Oracle 19c installation on Linux 7. We will be using Oracle Linux 7 and Oracle Database 19.3 softwares for this activity. We will also be looking at database creation via DBCA. Oracle 19c Pre-requisites Install Oracle 19c Software DBCA Create 19c Container Database DBCA Create 19c Database (non-cdb) Oracle 19c Pre-requisites Use the YUM repository to perform all the pre-install steps. Make sure your VM is able to ping google.com before executing below command yum install -y oracle-database-preinstall-19c Set password for Oracle user passwd oracle Create Oracle home directory and give ownership to Oracle user mkdir -p /u01/app/oracle/product/19.3/db_home chown -R oracle:oinstall /u01 chmod -R 775 /u01 Setup Oracle user bash_profile su - oracle vi .bash_profile Delete all and paste below. Make sure to change environment variables according to your environment # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs export ORACLE_SID=CDB export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/19.3/db_home PATH=$PATH:$HOME/.local/bin:$ ORACLE_HOME /bin export PATH Export bash profile . .bash_profile Install Oracle 19c Software Download Oracle 19c and copy the 19c software file to ORACLE_HOME location and unzip. Start the run installer to perform installation cd $ORACLE_HOME unzip -qo /softwares/LINUX.X64_193000_db_home.zip #for GUI installation ./runInstaller #for silent installation ./runInstaller -ignorePrereq -waitforcompletion -silent \ -responseFile ${ORACLE_HOME}/install/response/db_install.rsp \ oracle.install.option=INSTALL_DB_SWONLY \ ORACLE_HOSTNAME=${HOSTNAME} \ UNIX_GROUP_NAME=oinstall \ INVENTORY_LOCATION=/u01/app/oraInventory \ SELECTED_LANGUAGES=en,en_GB \ ORACLE_HOME=${ORACLE_HOME} \ ORACLE_BASE=${ORACLE_BASE} \ oracle.install.db.InstallEdition=EE \ oracle.install.db.OSDBA_GROUP=dba \ oracle.install.db.OSBACKUPDBA_GROUP=dba \ oracle.install.db.OSDGDBA_GROUP=dba \ oracle.install.db.OSKMDBA_GROUP=dba \ oracle.install.db.OSRACDBA_GROUP=dba \ SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \ DECLINE_SECURITY_UPDATES=true DBCA Create 19c Container Database To create 19c container database along with multiple PDBs, use below dbca -silent -createDatabase \ -templateName General_Purpose.dbc \ -gdbname ${ORACLE_SID} -sid ${ORACLE_SID} \ -characterSet AL32UTF8 \ -sysPassword enterCDB#123 \ -systemPassword enterCDB#123 \ -createAsContainerDatabase true \ -totalMemory 2000 \ -storageType FS \ -datafileDestination /u01/${ORACLE_SID} \ -emConfiguration NONE \ -numberOfPDBs 2 \ -pdbName PDB \ -pdbAdminPassword enterPDB#123 \ -ignorePreReqs Check all the containers inside the database sqlplus / as sysdba SELECT NAME , OPEN_MODE , CDB FROM V$DATABASE ; SELECT CON_ID, NAME, OPEN_MODE FROM V$CONTAINERS; DBCA Create 19c Database (non-cdb) Oracle has stopped supporting non-cdb architecture but you can still create a non-cdb database export ORACLE_SID=orcl #for silent database creation dbca -silent -createDatabase \ -templateName General_Purpose.dbc \ -gdbname ${ORACLE_SID} -sid ${ORACLE_SID} \ -characterSet AL32UTF8 \ -sysPassword enterDB#123 \ -systemPassword enterDB#123 \ -createAsContainerDatabase false \ -totalMemory 2000 \ -storageType FS \ -datafileDestination /u01/${ORACLE_SID} \ -emConfiguration NONE \ -ignorePreReqs -sampleSchema true Further Read Oracle 19c new features Oracle Database 19c Documentation Become a top notch DBA. Unlimited Courses & Xperiments! Browse Courses

Linux SQL Oracle Oracle Database Oracle ASM Oracle RAC Oracle Golden Gate Enterprise Manager MySQL PostgreSQL AWS Home Install Oracle 19c with ASM Complete guide to installing Oracle 19c with Automatic Storage Management (ASM). In this article we would be looking at installing Oracle 19c with ASM on Linux. This will allow us to create 19c databases on ASM disks. Prerequisites Setup ASM Disks Install Oracle 19c Grid Install Oracle 19c Database DBCA Create Database Silent Mode Prerequisites Start by installing required packages using yum repository yum -y install oracle-database-preinstall-19c.x86_64 yum -y install wget oracleasm kmod-oracleasm oracleasm-support It's nice keep your Linux system up-to-date yum -y update yum clean all Create required groups and the grid user groupadd -g 54327 asmdba groupadd -g 54328 asmoper groupadd -g 54329 asmadmin useradd -u 54322 -g oinstall -G dba,asmdba,asmoper,asmadmin grid For the oracle user to have access to ASM, it must be part of asm groups usermod -u 500 -g oinstall -G dba,oper,asmdba,asmoper,asmadmin,kmdba,dgdba,backupdba,racdba oracle Let's create directories for grid home, oracle home and oracle inventory mkdir -p /u01/app/grid/19c/grid_home mkdir -p /u01/app/oracle/19c/db_home mkdir -p /u01/app/oraInventory Give ownership of each directory to respective users chown -R oracle:oinstall /u01 chown -R grid:oinstall /u01/app/grid chown -R grid:oinstall /u01/app/oraInventory Setup ASM Disks At this stage, we can proceed with ASM disks configuration and initialise it oracleasm init oracleasm configure -i Default user to own the driver interface []: grid Default group to own the driver interface []: oinstall Start Oracle ASM library driver on boot ( y / n )[n]: y Scan for Oracle ASM disks on boot ( y / n ): y Writing Oracle ASM library driver configuration : done For the demo purpose, I have attached 30gb disk to the server and create three partitions of 10gb each for CRS, DATA and FRA Find the list of disks attached to the server fdisk -l Format the newly attached 30gb disk fdisk /dev/vdc n +10G n w --> to save At this stage you should have three partitions of 10gb each fdisk -l /dev/vdc Disk /dev/vdc: 32.2 GB, 32212254720 bytes, 62914560 sectors Units = sectors of 1 * 512 = 512 bytes Sector size (logical/physical): 512 bytes / 512 bytes I/O size (minimum/optimal): 512 bytes / 512 bytes Disk label type: dos Disk identifier: 0x562c57ef Device Boot Start End Blocks Id System /dev/vdc1 2048 20973567 10485760 83 Linux /dev/vdc2 20973568 41945087 10485760 83 Linux /dev/vdc3 41945088 62914559 10484736 83 Linux Create three ASM disks with each partition oracleasm createdisk CRS1 /dev/vdc1 oracleasm createdisk DATA1 /dev/vdc2 oracleasm createdisk FRA1 /dev/vdc3 oracleasm scandisks oracleasm listdisks Install Oracle 19c Grid Switch to grid user and edit the .bash_profile su - grid vi .bash_profile Make sure to add ORACL_BASE, ORACLE_HOME and edit PATH to include ORACLE_HOME . Once done, your .bash_profile should look like below # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs export ORACLE_BASE=/u01/app/grid export ORACLE_HOME=/u01/app/grid/19c/grid_home PATH=$PATH:$HOME/.local/bin:$ ORACLE_HOME /bin export PATH Create a function under .bashrc file so we can use it to quickly set environment variables for ASM instance vi .bashrc Put below at the end of the file ASM() { export ORACLE_SID=+ASM echo "ORACLE_SID="$ORACLE_SID echo "ORACLE_BASE="$ORACLE_BASE echo "ORACLE_HOME="$ORACLE_HOME } The ASM function will help us quickly set ORACLE_SID=+ASM so we don't have to export it manually Execute the bash_profile once to make environmental variables changes . .bash_profile ASM --> to setup ASM variables Download Oracle 19c grid and copy the 19c software file to ORACLE_HOME location and unzip. Start the run installer to perform installation cd $ORACLE_HOME unzip LINUX . X64_193000_grid_home . zip Start the gridSetup.sh to install grid ./gridSetup.sh Follow the on screen instructions to complete Oracle 19c grid setup. Install Oracle 19c Database Before we proceed to install Oracle 19c database, start dbca and create DATA and FRA diskgroups to hold DB files and Fast Recovery Area Switch to oracle user and edit the .bash_profile su - oracle vi .bash_profile Make sure to add ORACL_BASE, ORACLE_HOME and edit PATH to include ORACLE_HOME . Once done, your .bash_profile should look like below # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/19c/db_home PATH=$PATH:$HOME/.local/bin:$ ORACLE_HOME /bin export PATH Create a function under .bashrc file so we can use it to quickly set environment variables for ASM instance vi .bashrc Put below at the end of the file orcl() { export ORACLE_SID=orcl echo "ORACLE_SID="$ORACLE_SID echo "ORACLE_BASE="$ORACLE_BASE echo "ORACLE_HOME="$ORACLE_HOME } The orcl function will help us quickly set ORACLE_SID=orcl so we don't have to export it manually Execute the bash_profile once to make environmental variables changes . .bash_profile orcl --> to setup orcl variables Download Oracle 19c and copy the 19c software file to ORACLE_HOME location and unzip. Start the run installer to perform installation cd $ORACLE_HOME unzip LINUX.X64_193000_db_home.zip #for GUI installation ./runInstaller #for silent installation ./runInstaller -ignorePrereq -waitforcompletion -silent \ -responseFile ${ORACLE_HOME}/install/response/db_install.rsp \ oracle.install.option=INSTALL_DB_SWONLY \ ORACLE_HOSTNAME=${HOSTNAME} \ UNIX_GROUP_NAME=oinstall \ INVENTORY_LOCATION=/u01/app/oraInventory \ SELECTED_LANGUAGES=en,en_GB \ ORACLE_HOME=${ORACLE_HOME} \ ORACLE_BASE=${ORACLE_BASE} \ oracle.install.db.InstallEdition=EE \ oracle.install.db.OSDBA_GROUP=dba \ oracle.install.db.OSBACKUPDBA_GROUP=dba \ oracle.install.db.OSDGDBA_GROUP=dba \ oracle.install.db.OSKMDBA_GROUP=dba \ oracle.install.db.OSRACDBA_GROUP=dba \ SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \ DECLINE_SECURITY_UPDATES=true Once the installation is done, run the root scripts and you are now ready to create a database on ASM using dbca DBCA Create Database Silent Mode You could choose to invoke dbca in graphical mode or run it in silent mode dbca -silent -createDatabase \ -templateName General_Purpose.dbc \ -gdbname ${ORACLE_SID} -sid ${ORACLE_SID} \ -characterSet AL32UTF8 \ -sysPassword enterDB#123 \ -systemPassword enterDB#123 \ -createAsContainerDatabase false \ -totalMemory 2000 \ -storageType ASM \ -datafileDestination +DATA \ -recoveryAreaDestination +FRA \ -emConfiguration NONE \ -ignorePreReqs -sampleSchema true Become a top notch DBA. Unlimited Courses & Xperiments! Browse Courses

Linux SQL Oracle Oracle Database Oracle ASM Oracle RAC Oracle Golden Gate Enterprise Manager MySQL PostgreSQL AWS Home Display time in a Date column Learn how to extract and display time from a date column in SQL. By default, when you query a date column, oracle will only display dates and not time. Below query enables Oracle to display both date and time for a particular session alter session set nls_date_format='dd-Mon-yyyy hh:mi:sspm'; Note: this is only session level query. Become a top notch DBA. Unlimited Courses & Xperiments! Browse Courses

Linux SQL Oracle Oracle Database Oracle ASM Oracle RAC Oracle Golden Gate Enterprise Manager MySQL PostgreSQL AWS Home Find SQL execution plan change Detect and analyze changes in SQL execution plans in Oracle. 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; Become a top notch DBA. Unlimited Courses & Xperiments! Browse Courses

Linux SQL Oracle Oracle Database Oracle ASM Oracle RAC Oracle Golden Gate Enterprise Manager MySQL PostgreSQL AWS Home Shell Script to Convert Distance Units Convert distances between units using a simple shell script. Linux shell script allows you to calculate the user input number to display in different units. This will be achieved by using the bc command. bc command is used for the command-line calculator. vi convert_distance.sh # !/bin/bash echo "Enter distance (in km) : " read km meter=`echo $km \* 1000 | bc` feet=`echo $meter \* 3.2808 | bc` inches=`echo $feet \* 12 | bc` cm=`echo $feet \* 30.48 | bc` echo "Total meter is : $meter " echo "Total feet is : $feet " echo "Total inches is : $inches " echo "Total centimeters : $cm " Here is the sample output of the above script More ways to use the conversion You also use the if-else statement to use the unit conversion. You can ask the user to make the selection of the desired conversion unit and provide the output accordingly. Become a top notch DBA. Unlimited Courses & Xperiments! Browse Courses

Linux SQL Oracle Oracle Database Oracle ASM Oracle RAC Oracle Golden Gate Enterprise Manager MySQL PostgreSQL AWS Home Delete RMAN Backups Safely delete obsolete RMAN backups to free up storage. There are many ways to delete RMAN backups and as a DBA you must know the different ways so that you can keep RMAN and OS disk space clean. Delete All Backups Delete Archivelog Backup Delete Backup Tab Delete Expired Backups Delete Obsolete Backups Delete Archivelog While Backup Delete All Backups To delete all the backups for the target database, use below command RMAN> DELETE BACKUP; The above command will prompt you to confirm the deletion. In case you are sure to delete the backups and want to skip the confirmation prompt then use below command RMAN> DELETE NOPROMPT BACKUP; Delete Archivelog Backup To delete archivelog files from disk (NOT BACKUP), use below commands RMAN> DELETE ARCHIVELOG ALL; RMAN> DELETE ARCHIVELOG UNTIL SEQUENCE 300; RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE ‘sysdate-1’; RMAN> DELETE ARCHIVELOG ALL BACKED UP 2 TIMES to disk; To delete archivelog files from the RMAN backups (NOT DISK), use below commands RMAN> DELETE BACKUP OF ARCHIVELOG ALL; RMAN> DELETE BACKUP OF ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7'; Delete Backup Tag If you triggered a backup and want to delete it completely, you can specify the backup tag and rman will delete all the backup files associated with tag RMAN> DELETE BACKUP TAG ; Delete Expired Backups First run RMAN CROSSCHECK command and then use below command to delete all the expired backups RMAN> CROSSCHECK BACKUP; RMAN> DELETE EXPIRED BACKUP; RMAN> DELETE EXPIRED ARCHIVELOG ALL; Delete Obsolete Backups You can run REPORT OBSOLETE command to see all the rman backups that are no longer required for database recovery. Use below command to delete all the RMAN obsolete backups RMAN> DELETE OBSOLETE; Delete Archivelog While Backup You can use DELETE INPUT clause to delete files while taking RMAN backups RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT; RMAN> BACKUP ARCHIVELOG ALL FROM SEQUENCE 1200 DELETE ALL INPUT; Become a top notch DBA. Unlimited Courses & Xperiments! Browse Courses

Linux SQL Oracle Oracle Database Oracle ASM Oracle RAC Oracle Golden Gate Enterprise Manager MySQL PostgreSQL AWS Home Oracle Data Guard Physical Standby Configuration Step-by-step guide to configure a physical standby database. There are various steps in which you can configure physical standby database . We need to make several changes to the primary database before we can even setup the standby database. In this article, we will set up physical standby on Oracle Linux 7.6 server. Physical Standby Configuration Overview Primary database changes Configure network Configure redo transport Build standby Verify standby configuration This article applies to Oracle 12c R2 database version Physical Standby Configuration Overview Primary details SID: ip7 ORACLE_HOME: /u01/app/oracle/product/12.2.0.1 Host Name: srv1.dbagenesis.com Standby details SID: ip7 ORACLE_HOME: /u01/app/oracle/product/12.2.0.1 Host Name: srv2.dbagenesis.com Assumption: we assume that primary server has a database (SID=ip7) up and running. The standby database has Oracle 12cR2 installation done in the same oracle home location as primary. Primary database changes Primary must run in archive log mode. Check the archive log mode SELECT log_mode FROM v$database; LOG_MODE ------------ NOARCHIVELOG If it is not running in archive log mode, then enable it SQL> shutdown immediate SQL> startup mount SQL> alter database archivelog; SQL> alter database open; SQL> archive log list; Enable force logging on primary : In oracle, users can restrict redo generation for SQL by using NOLOGGING clause. This NOLOGGING transaction will be a problem for physical standby. Hence, we force logging so even user uses NOLOGGING clause, every SQL will be logged on to redo SQL> alter database force logging; SQL> select name, force_logging from v$database; Standby file management: We need to make sure whenever we add/drop datafile in primary database, those files are also added / dropped on standby SQL> alter system set standby_file_management = 'AUTO'; Create standby log files: You must create standby log files on primary. These files are used by a standby database to store redo it receives from primary database. Our primary may become standby later and we would need them, so better to create it. First check the current log groups SQL> select GROUP#, THREAD#, bytes/1024/1024, MEMBERS, STATUS from v$log; GROUP# THREAD# BYTES/1024/1024 MEMBERS STATUS ---------- ---------- --------------- ---------- ---------------- 1 1 200 1 INACTIVE 2 1 200 1 CURRENT 3 1 200 1 INACTIVE SQL> select member from v$logfile; MEMBER --------------------------------------------------- /u01/data/db_files/ip7/redo03.log /u01/data/db_files/ip7/redo02.log /u01/data/db_files/ip7/redo01.log Add the standby logfiles, make sure group number should be from a different series like in this case we choose to start with 11 and above. This helps in easy differentiation. Make sure to keep the thread# and logfile size exactly same. Oracle also recommends to always create n+1 standby log files. Where n is the total number of logfiles ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11 '/u01/data/db_files/ip7/stb_redo1.log' SIZE 200M; ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 '/u01/data/db_files/ip7/stb_redo2.log' SIZE 200M; ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 '/u01/data/db_files/ip7/stb_redo3.log' SIZE 200M; ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 14 '/u01/data/db_files/ip7/stb_redo4.log' SIZE 200M; Check the standby log files via below query SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG; Enable flashback on primary: Flashback database is highly recommended because in case of failover, you need not re-create primary database from scratch SQL> alter system set db_recovery_file_dest_size=45g; SQL> alter database flashback on; SQL> select flashback_on from v$database; If flashback parameters are not set properly, use below commands SQL> show parameter recovery; SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'; SQL> alter system set db_recovery_file_dest_size=45g; SQL> alter database flashback on; Check DB Unique name parameter on primary: Make sure your primary database has DB_UNIQUE_NAME parameter set for consistency. If it’s not set properly, use ALTER SYSTEM SET command SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------- db_name string ip7 SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------- db_unique_name string ip7 Configure network Use below tns entries and put them under ORACLE user HOME/network/admin/tnsnames.ora. Change host as per your environment and execute on both primary and standby. Notice the use of the SID, rather than the SERVICE_NAME in the entries. This is important as the broker will need to connect to the databases when they are down, so the services will not be present. vi $ORACLE_HOME/network/admin/tnsnames.ora ip7 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = srv1.dbagenesis.com)(PORT = 1521)) ) (CONNECT_DATA = (SID = ip7) ) ) ip7_stb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = srv2.dbagenesis.com)(PORT = 1521)) ) (CONNECT_DATA = (SID = ip7) ) ) Configure listener on primary database. Since the broker will need to connect to the database when it’s down, we can’t rely on auto-registration with the listener, hence the explicit entry for the database. vi $ORACLE_HOME/network/admin/listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = srv1.dbagenesis.com)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ip7_DGMGRL) (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1) (SID_NAME = ip7) ) ) ADR_BASE_LISTENER = /u01/app/oracle Configure listener on standby. Since the broker will need to connect to the database when it’s down, we can’t rely on auto-registration with the listener, hence the explicit entry for the database. vi $ORACLE_HOME/network/admin/listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = srv2.dbagenesis.com)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ip7_stb_DGMGRL) (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1) (SID_NAME = ip7) ) ) ADR_BASE_LISTENER = /u01/app/oracle Once the listener.ora changes are in place, restart the listener on both servers lsnrctl stop lsnrctl start Configure redo transport Note: if you plan to use Oracle Data Guard broker, then you can skip this section “configure redo transport” and jump to “Build Standby” section. Configure redo transport from primary to standby: The below statement says that if the current database is in primary role, then transport logs to standby. We need to change service and db_unique_name for same parameter on standby server On Primary Server ================= SQL> alter system set log_archive_dest_2 = 'service=ip7_stb async valid_for=(online_logfiles,primary_role) db_unique_name=ip7_stb'; Set FAL_SERVER: Fetch Archive Log parameter tells primary as to where it will get archives from On Primary Server ================= SQL> alter system set fal_server = 'ip7_stb'; Set dg_config parameter: This parameter defines which databases are in data guard configuration On Primary Server ================= SQL> alter system set log_archive_config = 'dg_config=(ip7,ip7_stb)'; Build standby Create pfile on primary, open it and create the necessary directories on the standby server On Primary Server ================= SQL> create pfile from spfile; exit $ cd $ORACLE_HOME/dbs $ cat initip7.ora On Standby Server ================= Create directories as you find in the initip7.ora file mkdir -p /u01/app/oracle/admin/ip7/adump mkdir -p /u01/data/db_files/ip7 mkdir -p /u01/FRA/ip7 On standby server, create parameter file with just db_name parameter and start the instance in nomount mode On standby server ================= vi $ORACLE_HOME/dbs/initip7.ora *.db_name='ip7' $ export ORACLE_SID=ip7 $ sqlplus / as sysdba SQL> STARTUP NOMOUNT; SQL> exit; --you must exit from sqlplus, else cloning will fail Copy password file: Copy the password file from primary to standby server $ scp orapwip7 oracle@srv2:$ORACLE_HOME/dbs If no password file exists, create one via below command and then copy orapwd file=$ORACLE_HOME/dbs/orapwip7 force=y Duplicate primary database via RMAN: On primary, connect to RMAN, specifying a full connect string for both the TARGET and AUXILIARY instances. Do not attempt to use OS authentication else, the cloning will fail To DORECOVERY option starts recovery by applying all available logs immediately after restore On primary server ================= rman target sys@ip7 auxiliary sys@ip7_stb RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER SPFILE SET db_unique_name='ip7_stb' SET fal_server='ip7' SET log_archive_dest_2='service=ip7 async valid_for=(online_logfiles,primary_role) db_unique_name=ip7' NOFILENAMECHECK; Once cloning is done, you should see below at RMAN prompt Finished Duplicate Db at 07-DEC-2015 Enable flashback on standby: As we know the importance of flashback in data guard, we must enable it on standby as well On Standby Server ================= SQL> alter database flashback on; Bounce database & start MRP (Apply Service): It's good to bounce standby, put it in mount mode and start MRP process On Standby Server ================= SQL> shut immediate; SQL> startup mount; SQL> alter database recover managed standby database disconnect; Verify standby configuration Once MRP is started, we must verify which archive log number MRP is applying on standby On standby: =========== select process, status, sequence# from v$managed_standby; select sequence#, applied, first_time, next_time, name filename from v$archived_log order by sequence#; Below queries will help you identify issues when your data guard setup is out of sync On both primary & standby: ========================== set lines 999; select * from v$dataguard_status order by timestamp; select dest_id, status, destination, error from v$archive_dest where dest_id<=2; IF you see ORA-16058, do this on primary: ========================================= SQL> alter system set log_archive_dest_state_2='DEFER'; SQL> alter system set log_archive_dest_state_2='ENABLE'; SQL> select dest_id, status, destination, error from v$archive_dest where dest_id<=2; On primary: =========== select sequence#, first_time, next_time, applied, archived from v$archived_log where name = 'ip7_stb' order by first_time; select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2; archive log list; Configure Archive deletion policy: We must set this policy in order to prevent accidental deletion of archive logs on primary database On Primary: =========== rman target / configure archivelog deletion policy to applied on all standby; Become a top notch DBA. Unlimited Courses & Xperiments! Browse Courses