MyNotes

Let us discuss "Rman Parameter  configuration Part -2 in oracle11g" this are basic steps before we take backup for more details click here https://ampersandacademy.com/tutorials/oracle-admin-2/rman-configuration-parameter-part-2

Let us we discuss How to understand AWR reports in oracle 11g? We already Saw how to generate AWR reports here we go to read AWR reports step by step https://ampersandacademy.com/tutorials/oracle-admin-1/how-to-understand-awr-report-in-oracle11g

AWR VS STATSPACK   *When we are facing performance issue in the database our first thought is to take AWR or statspack reports. but we do not know what are the difference between AWR and statspack Here we can discuss them and I will try to mention as much as I can   https://ampersandacademy.com/tutorials/oracle-admin-1/awr-vs-statspack-reports-in-oracle-11g

Let us we discuss Dataguard and Rac Background Process in oracle? Here we can see some important background process in Dataguard and RAC in oracle 11g https://ampersandacademy.com/tutorials/oracle-admin-1/dataguard-and-rac-background-processes-in-oracle-11g

Let us we discuss What are the background process in oracle ?  In architecture we already saw some mandatory (PMON,SMON,DBWR,LGWR,CHPK AND ARCN )background process,but here we are going to see some more background process for more details click here https://ampersandacademy.com/tutorials/oracle-admin-1/background-process-in-oracle

High availability and scalability: * As a dba most scraiest word or scariest area is down time we need to expertise in this particular ares. *in that down time is the test to dba capcity only we can resolve by expertise and practice comes into picture at that time we can't google how do fail over and switch over? reason of downtime:: *as planned down time for os level paramter or the server team doing migaration old into new hardware Planned Down time *Its for doing hardware or software level ex: applying patches ,Server Hardware Upgrade,Server maintenance. Unplanned downtime: * 1)computer failure - our server completely blast,before the computer failure data failures causes the downtime. Types of downtime:: *storage failure- let us take harddisk is data stored its totally gone *site failure - due to some natural disaster our production server is gone *Human error - someone deleting the data from database *corruption - one block or sector are corrupted in h...

overview of Oracle RAC SMP vs cluster single processor machine(symmetric multiprocessing): *It involves a multiprocessor computer hardware architecture where two or more identical processors are connected to a single shared main memory and are controlled by a single OS instance. *SMP is single processing machine / single processor machine basically all of our desktops, laptop because it have dedicated ram, dedicated hard disk, and processor *It acts as one single system Cluster: *it consists of multiple systems which are connected each other to solve the business problem * ex: if we search the query in google it means the single system responds to my query, actually it behind a lot of servers are connected and gives respond for users request outside of the world it seems one big system. Cluster Management software: *If we are going to use word file we need ms office software, at the same, we are going to access pdf files we need the pdf reader like the same we n...

How to resize the redo logfile /online redo logfile in Oracle 11g? Let us we discuss How to resize the redo logfile /online redo logfile in Oracle 11g? The redo logfile contains the history of changes data in database, Oracle allows you to have more than one copy of each redo log file, This important feature is called "multiplexing redo log files." The steps to be followed to resize the redo logfile Kindly click here https://ampersandacademy.com/tutorials/oracle-admin-1/resize-the-redo-logfile-online-redo-logfile

Let us we discuss how to check sga total size & free space in Oracle11g ? 1)Total Size: * The view (V$sga) display summary information about the system global area (SGA). for more details kindly click here https://ampersandacademy.com/tutorials/oracle-admin-1/check-sga-total-size-and-free-space-in-oracle11g

let us we discuss how to find the undo tablespace expired /unexpired in oracle11g SQL> SELECT tablespace_name, status, COUNT(*) AS HOW_MANY   2  FROM dba_undo_extents   3  GROUP BY tablespace_name, status; TABLESPACE_NAME                STATUS      HOW_MANY ------------------------------             -------------       -------------------- UNDOTBS1                            UNEXPIRED         13 UNDOTBS1                            EXPIRED     ...

Let we discuss how to check the tablespace creation time in Oracle 11g? *By using this query we will get the tablespace name, status and creation time   SQL> col FILE_NAME for a45 SQL> col tablespace_name for a10 SQL> col status for a15 SQL> col creation_time for a15 SQL> set lines 999 pages 100 SQL> select a.File_name,a.Tablespace_name,a.status,b.creation_time from dba_data_files a inner join V$datafile b on a.file_name=b.Name group by a.File_name, a.Tablespace_name,a.status,b.creation_time ; FILE_NAME TABLESPACE STATUS CREATION_TIME --------------------------------------------- ---------- --------------- --------------- C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF SYSTEM AVAILABLE 27-AUG-17 C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF USERS AVAILABLE 27-AUG-17 C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF UNDOTBS1 AVAILABLE 27-AUG-17 C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SAM01.DBF...

let we discuss how to find Log switches in Database with Details of oracle11g Note: V$LOG_HISTORY displays log history information from the control file. Method-1: I am going to take log switches count till before the month end (i.e current date is '27-Sep-17' using last_day function  it consider the date is '30-Sep-2017') SQL> col c1 for a10 heading "Month" SQL> col c2 for a25 heading "ArchiveDate" SQL> col c3 for 999 heading "Switches" SQL> SQL> col c1 for a10 heading "Month" SQL> col c2 for a25 heading "ArchiveDate" SQL> col c3 for 999 heading "Switches" SQL> select to_char(trunc(first_time),'Month') c1, to_char(trunc(first_time),'DAY:DD-MM-YY') c2, count(*) c3 from V$log_history where first_time > last_day(trunc (sysdate) - 30)+1 group by trunc(first_time) order by trunc(first_time); Month ArchiveDate Switches ---------- ---------------...

Let us we discuss How to monitor our data pump jobs in oracle11g? *Datapump jobs are monitor into few views maintained within the Oracle instance(Datapump is running) *These views are DBA_DATAPUMP_JOBS, DBA_DATAPUMP_SESSIONS, and V$SESSION_LOGOPS. https://ampersandacademy.com/tutorials/oracle-admin-2/how-to-monitor-data-pump-jobs

1.Let us we discuss what is purpose we have to learn Oracle RAC11g? *Before we are discussing what is OracleRAC and what are the benefits of RAC we will the life cycle of " OracleDba " has shown in the pic below *The most people are reading this blog the who is already oracle dba or they are trying to become an oracle dbathe person who is started his career at Linux administrator or he had a good knowledge of Linux than he stepped into oracle dba (most of them knows what is oracle dba and what they are doing we are not discussing about entire thing but we will focus outline ) *It classified into two types 1)physical dba 2)Logical dba for more details https://ampersandacademy.com/tutorials/oracle-rac-11g/why-we-need-to-learn-oracle-rac11g

How to Create additional database in Oracle Express edition or How to create manual database in windows   *In my windows i have installed oracle11g express edition i want to create new database for my testing purpose but the express edition doesnot support DBCA utiltiy let us we can discuss How to Create additional database in Oracle Express edition or How to create manual database in oracle11g windows enivornment. https://ampersandacademy.com/tutorials/oracle-admin-2/create-additional-database-oracle-express-edition--create-manual-database

Today i am tired to export  one schema (hr) from the one database  into another database  ( import schema ) so i created one directory which is called "ram" grant read and write access to  the system user ,then i started export the schema (hr) from production  while i did the export of hr schema the job was successful after that i did import schema like below expdp : C:\>expdp system/oracle directory=ram schemas=hr dumpfile=hr.dmp logfile=hr.log ...... ...... Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:   C:\datapump\HR.DMP Job "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully completed at 13:23:40 impdp: C:\>impdp system/oracle directory=ram remap_schema=hr:scott dumpfile=HR.DMP logfile=hr.log it through an error like below . ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-31619: invalid dump file "C:\datapump\hr.dmp" ORA-27091: unable to queue I/O ORA-27070: async read/write failed OSD-04006: ReadFile() failu...

 While i am installing the oracle 11g database i got the popup message " Error in starting the service.The service OracleMTSRecoveryService was not found " i just ignored the message and continue my installing but the installation was successful with errors, so once again i started the installation this time also i got the same error pop up and  searched on google to resolve this issue by using below steps S-1: S-2: press " Ctrl + r " then type " regedit " you 'll got this screen and  then choose " HKEY_LOCAL_MACHINE "   S-3:  Then choose " SYSTEM "  => " ControlSet001 " => " Services " => " OracleMTSRecoveryService "       S-4:                 After   choosing the " OracleMTSRecovery " we can see the " imagepath " which was mentioned below Double click the image and change the path where we are going to install the software(like be...

*Let us we discuss oracle 12c architecture and explanation before you must have the knowledge on Oracle 11g architecture if you have good knowledge on the oracle 11g architecture it's easy to understand 12c *Before that 12c Multiple databases reside on single server and single database contains multiple schemas apart from RAC each Oracle database had single instance, but in 12c it supports Many databases to one instance   What is multitenant Architecture? *The Multiple tenants share the same source of a mutual benefit for different purposes at a very broad level like the same applies to Oracle database which means multiple databases can share a single database instance resources for different purposes on the same server. *The Oracle 12c which is built on mulitenant foundation is called container database (CDB) and each container (CDB) resides inside is called pluggable database https://ampersandacademy.com/tutorials/oracle-12c

B-Tree cluster indexes: *This indexes(indexed cluster) is a table cluster that uses an index to locate data. *The cluster index is a B-tree index on the cluster key ,It must be created before any rows can be instered into clustered tables * A cluster is a group tables that share the same data blocks i.e all the tables are physically stored together Create cluster: Let us we can assume to create cluster based on the cluster key department_id SQL> create cluster emp_dept (department_id number(4)); Cluster created. Then create index on it SQL> create index idx_emp_dept on cluster emp_dept; Index created. Then create table emp and dept SQL> create table emp (department_id number(4), 2 name varchar2(20), 3 empno number(5), 4 sal number(10,2)) 5 cluster emp_dept (department_id); Table created. SQL> insert into emp values(10,'ram',123,'1500'); 1 row created. SQL> insert into emp values(20,'sam...

today I got a question from Oracle dba group which background process will start first when the instance is started? (When considering in oracle 10g) Many of them gave the answer is "smon" but it was the wrong answer one of the group members answered psp0 (PROCESS SPAWNER) then I searched on google about PSP process and here we can see about PSP(PROCESS SPAWNER) process. kindly check the link below https://ampersandacademy.com/tutorials/oracle-admin-1/psp0-process-label-name

Let us we discuss about today  session the type of B-tree index (reverse key)  kindly check the link below https://ampersandacademy.com/tutorials/oracle-admin-2/types-of-b-tree-indexes-reverse-key-index

Let us we discuss about today  session How to find unused index in oracle 11g  kindly check the link below https://ampersandacademy.com/tutorials/oracle-admin-2/how-to-find-unused-index-in-oracle-11g

Let us we discuss about today  session Types of Btree (Descending and Key compressed) index In Oracle 11g kindly check the link below https://ampersandacademy.com/tutorials/oracle-admin-2/types-of-btree-descending-and-key-compressed-index

Let us we discuss about today  session Introduction of index in oracle 11g kindly check the link below https://ampersandacademy.com/tutorials/oracle-admin-2/introduction-of-index-in-oracle-11g

Let us we discuss about What is recovery catalog? how it 'll uses in Rman? How to create Catalog? How to register target database in recovery catalog? for more details click here   https://ampersandacademy.com/tutorials/oracle-admin-2/recovery-catalog-creation-and-registration-target-db-in-oracle-11g

Let Us we discuss about V$views are using Rman Jobs and  How the V$views are more useful in Rman Jobs? for more details click the link https://ampersandacademy.com/tutorials/oracle-admin-2/v-views-for-using-rman-jobs

 Let Us see Crosscheck command uses in Rman kindly check it for more details click the link https://ampersandacademy.com/tutorials/oracle-admin-2/rman-crosscheck-command-in-oracle

Let us we discuss about Awr(Automatic Workload Repository) report in oracle in this topic we ll see what is AWR? What is the use of AWR report? How to create AWR report  by manually and Automatically? for more details click https://ampersandacademy.com/tutorials/oracle-admin-1/awr-report-in-oracle-11g     

Let we discuss about Alert Log in oracle11g in this post so what is alert log? How can we use alert in oracle database? for more details click https://ampersandacademy.com/tutorials/oracle-admin-1/alert-log-in-oracle11g

Let Us we see the topic of "Rman Validate " command in oracle11g and how it can used to check physical and logical corruption? for more details click the link https://ampersandacademy.com/tutorials/oracle-admin-2/rman-validate-commands-in-oracle11g

Let we discuss about Report operation on Rman in oracle11g and We can also see the difference between "LIST" and "REPORT" in Rman click the link https://ampersandacademy.com/tutorials/oracle-admin-2/rman-reporting-operations-in-oracle-11g

Rman(Recovery Manager) is an Oracle Database client that performs backup and recovery tasks on your database and it automates  the work of backup strategies like backup, restore  and  recovering data files. and we can also discuss about Rman Types of Incremental Backup More details  click the link https://ampersandacademy.com/tutorials/oracle-admin-2/rman-incremental-backup-cumulative-differential-in-oracle-11g

this is a common question most of the interviewer asked how to find the particular schema/owner size ? or  how to get the over all schema/owner size in database? Over all owner size Query SQL> select OWNER,Segment_type,sum(bytes)/ 1024/1024/1024 "SIZE_IN_GB" from dba_segments where segment_type='TABLE' group by owner,segment_type order by owner;     Note: if we want to get particular schema/user size along table only (exclude of index,view ) use segment_type='TABLE' otherwise just remove this segment_type     Particular schema/owner size Query   SQL> select OWNER,sum(bytes)/1024/1024/ 1024 "SIZE_IN_GB" from dba_segments group by owner order by owner;          

we can find out the database size in two types  1) Actual database  size  2) Over all database  Size 1)Actual database size  Query SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files; 2)Over all Database size Query Overall database size is the sum of used space plus free space i.e. the size of the data files, temp files, log files and the control files. You can find out the total database size using simple query. This sql gives the total size in GB.   select ( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) + ( select nvl(sum(bytes),0)/1024/1024/ 1024 temp_size from dba_temp_files ) + ( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) + ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS) /1024/1024/1024  controlfile_size from v$controlfile) "Size in GB" from dual;

By using this query we can able to find the table size by below mentioned table format owner.tablename. Eg - hr.employees query: select segment_name,segment_type, sum(bytes/1024/1024/1024) GB from dba_segments where segment_name='&Your_Table_Name' group by segment_name,segment_type;