ORACLE DBA BLOG

 How Do you switch Database from using Pfile to spfile ? What is the Size of a segment & how do you measure the size of segments ? What is different types of diagnostic files in Database ? what is audit file dump destiantion ? What is AMM &ASMM ? how can you find &  modify the location & size  to archive log generation destination  ? How do you create a backup for COntrolfile manaully ? How do you list the Size of SGA & PGA ? Explain The Difference Between $oracle_home And $oracle_base? When A User Process Fails, What Background Process Cleans Up After It? Which Prcess is responsible for Cleaning up buffers once the COmmit is done ?  How Would You Force A Log Switch?  What Is The Difference Between A Temporary Tablespace And A Permanent Tablespace?  Name A Tablespace Automatically Created When You Create A Database?  When Creating A User, What Permissions Must You Grant To Allow Them To Connect To The Database?  Name  Files Used For Network Connection To A Database?

 When you connect to the Oracle database, anything that has the DATE datatype, is displayed in the default format of DD-MON-YY, and no time component.   There are multiple ways to see the time component of a DATE column, in sqlplus. You can either alter your session to set the date format, or you can use a function in your select statement to alter the format of the column. alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS' ; select sysdate from dual;   SYSDATE ------------------- 12/01/2022 20:19:06   select to_char(sysdate, 'DD-MON-YY HH24:MI:SS' ) from dual;   TO_CHAR(SYSDATE,'D ------------------ 12-JAN-22 20:19:50   How about RMAN? How can you see the date and time a backup completed or started? By default you only see the same format as in sqlplus: DD-MON-YY. This format is not always enough. You try an alter session command in RMAN (same as above), and it doesn’t work. Instead, what is the solution? You must set the NLS_DATE_FORMAT

  DBAs use these two concepts interchangeably: table and segment, more precisely table segment. Why is that? When we think of tables, we think of the data inside that table. However when we think of the properties of the table, such as size, blocks, storage, etc. , we think about the table segment. When a user is asking us how big a table is, we do not correct them, to use the proper terminology of segment…So this is why we use these two things as if they were the same. However, they are not the same. A table is a basic unit of storage for the Oracle database. The table’s data is stored in rows and columns. You create a table by specifying one or more columns. Usually a table is created empty, unless you use the CTAS method. When you insert data into the table, you are creating rows for the table. Tables have names and an owner, they belong to a schema. You can select from a table to get the rows within the table, however you cannot determine the size of the tab

 The maximum number of processes in the database instance was reached, and I had to review if this is was an isolated case, or something that occurred often. There is a data dictionary view that keeps track of the number of processes in the instance for each hourly interval, or however often you take AWR snapshots. Since this view is a DBA_HIST view, and it is part of AWR, you need to have appropriate licensing in place, before you query it. You have been warned! I personally was not aware of this view! I won’t keep it from you any longer! The view name is: DBA_HIST_RESOURCE_LIMIT . This view contains snapshots of V$RESOURCE_LIMIT . The current value, the maximum utilization and the init parameter value for the resource are captured, along with other information. Knowing about this view, makes it easy to track the usage of processes over time, especially if your retention for AWR data is greater than the default of 8 days. Just a side note, for production sys

  Oracle instance recovery occurs automatically when you try to startup and open a database that crashed or was shutdown abort. An instance recovery is needed in this case, because the database is in an inconsistent state, meaning there are committed and uncommitted transactions present. Some committed transactions might not be written yet to the data files (these are only present in the online redo logs, and must be reapplied), and some data files might contain uncommitted changes (these must be rolled back). Why are committed and uncommitted changes present in datafiles? This is due to the way db writer or DBW background process writes the dirty buffers to data files. The dirty buffers in the buffer cache, could contain committed and uncommitted changes. How does Oracle know it needs to perform instance recovery? Do we need to explicitly specify it? When the database is open in read-write mode, a redo thread is also marked open in the control file. When the dat

 Normally in RMAN the date is show in format DD-MON-YY . we may require output to be more precise in format DD-MON-YY HH24:MI.    We cannot user sql "alter session set NLS_DATE_FORMAT"  . Instead we can use below command to extract output in desired output .  $ rman target /  RMAN>  host 'export NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"; $ORACLE_HOME/bin/rman target / catalog ****';  RMAN > list backup summary ;