Videos uploaded by user “ORACLE-DBA Workshops”
Simplified Oracle DBA 001- Download Oracle Virtual Box and Linux
To complete these demos and tutorials, students have to have to set up their own lab environment. I used Oracle Virtual Box and Oracle Linux 6 update 8 64 bit.
Simplified DBA-005-Installing database software 11g and 12c on Windows
we will install oracle 11g and then oracle 12c software only in Windows. ( Linux is covered in next demo ).
Simplified DBA-022-05-RMAN Data Recovery Advisor
Oracle provides Data Recovery Advisor that automatically gathers data failure information when an error is encountered. Based on failure, it can provide advice and commands on how to recover the error and can execute them also to recover the error. The Data Recovery Advisor could be used from RMAN and Enterprise Manager. LIST FAILURE Lists previously executed failure assessment ADVISE FAILURE Displays recommended repair option REPAIR FAILURE Repairs and closes failures (after ADVISE in the same RMAN session) CHANGE FAILURE Change or closes one or more failures
Demo on How to repair an Oracle BLOCK
Demo on How to repair an Oracle's corrupted block. in this demo we will simulate block corruption and then use RMAN to repair the block from available backups Following RMAN commands will be used list failure; advise failure; repair failure;
Simplified DBA-020- Exploring 11g Undo Advisor in Eneterprise Manager
UNDO Advisor in EM / dbcontrol Oracle provided UNDO Advisor in EM / Dbcontrol to estimate required size for your undo tablespace. It provides an estimate of the undo tablespace size required to satisfy a given undo retention
Simplified DBA-008-Creating a databases on Linux using DBCA
we will create two LISTENERS and two database First listener from 11g home for 11g database, then database 11g Second listener from 12c home for 12c database then database 12c
Simplified DBA-014- Database Architecture Physical and Logical   Explained
A database storage structure could be divided into logical and physical structure. Physical structure consists of files on physical disks while logical structure resides in the database. Both can be managed separately. Each database is logically divided into two or more tablespaces. One or more data files are explicitly created for each tablespace to physically store the data. A database is divided into logical storage units called tablespaces, which group related logical structures or datafiles together. For example, tablespaces commonly group all of an application’s segments to simplify some administrative operations. At the finest level of granularity, an Oracle database’s data is stored in data blocks. One data block corresponds to a specific number of bytes of physical space on the disk. A data block size is specified for each tablespace when it is created. A database uses and allocates free database space in Oracle data blocks. When you create a table, a segment is created with the same name in the tablespace. This segment has one or more Extents of equal size. Each Extent has Oracle Data Blocks as per tablespace definition. A data block could be of size 8K, 16K, 32K, 64K etc.
Simplified DBA 027- 01-Cloning Oracle Database using RMAN Backup and Duplicate command
Database cloning is an essential skill a DBA must have.
Simplified DBA-003-Install and Configure Oracle Linux
We will install Oracle Linux and configure it for database installation # groupadd oinstall # groupadd dba # groupadd oper # useradd –g oinstall –G dba,oper oracle # chown oracle:oinstall /u01 # chmod 775 /u01 rpm -ivh binutils- rpm -ivh binutils-devel- rpm -ivh compat-libcap1-1.10-1.x86_64.rpm rpm -ivh compat-libstdc++-33-3.2.3-69.el6.x86_64.rpm rpm -ivh gcc-4.4.7-17.el6.x86_64.rpm rpm -ivh gcc-c++-4.4.7-17.el6.x86_64.rpm rpm -ivh glibc-2.12-1.192.el6.x86_64.rpm rpm -ivh glibc-devel-2.12-1.192.el6.x86_64.rpm rpm -ivh ksh-20120801-33.el6.x86_64.rpm rpm -ivh libgcc-4.4.7-17.el6.x86_64.rpm rpm -ivh libgcc-4.4.7-17.el6.i686.rpm rpm -ivh libstdc++-4.4.7-17.el6.x86_64.rpm rpm -ivh libstdc++-devel-4.4.7-17.el6.x86_64.rpm rpm -ivh libaio-0.3.107-10.el6.i686.rpm rpm -ivh libaio-0.3.107-10.el6.x86_64.rpm rpm -ivh libaio-devel-0.3.107-10.el6.i686.rpm rpm -ivh libaio-devel-0.3.107-10.el6.x86_64.rpm rpm -ivh libXext-1.3.3-1.el6.x86_64.rpm rpm -ivh libXtst-1.2.2-2.1.el6.x86_64.rpm rpm -ivh libX11-1.6.3-2.el6.x86_64.rpm rpm -ivh libXau-1.0.6-4.el6.i686.rpm rpm -ivh libxcb-1.11-2.el6.x86_64.rpm rpm -ivh libXi-1.7.4-1.el6.x86_64.rpm rpm -ivh libX11-common-1.6.3-2.el6.noarch.rpm rpm -ivh libX11-1.6.3-2.el6.x86_64.rpm rpm -ivh libXext-1.3.3-1.el6.x86_64.rpm rpm -ivh libXi-1.7.4-1.el6.x86_64.rpm rpm -ivh libXtst-1.2.2-2.1.el6.x86_64.rpm rpm -ivh libXau-devel-1.0.6-4.el6.x86_64.rpm rpm -ivh libXau-1.0.6-4.el6.x86_64.rpm rpm -ivh libxcb-1.11-2.el6.x86_64.rpm rpm -ivh make-3.81-23.el6.x86_64.rpm rpm -ivh sysstat-9.0.4-31.el6.x86_64.rpm rpm -ivh unixODBC-2.2.14-14.el6.x86_64.rpm rpm -ivh unixODBC-devel-2.2.14-14.el6.x86_64.rpm $ cd /home/oracle $ gedit .bashrc Add following lines in .bashrc file export TMP=/tmp export TMPDIR=$TMP export ORACLE_HOSTNAME=host32.ansaridba.com export ORACLE_UNQNAME=DB12LIN export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/ export ORACLE_SID=DB12LIN export PATH=/usr/sbin:$PATH export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
Simplified DBA-023-01-SQL Plan Management   EXPLAIN PLAN
Optimizer needs statistics to create an execution plan for each query.
Simplified DBA 027- 02- Cloning Oracle Database using Duplicate from  ACTIVE Database  command
Demo : cloning oracle database from an Active Database ------------------------------------------------------ CLONING DB11LIN TO DB11CLN 1. Create pfile with ONE parameters only DB_NAME=DB11CLN 2. Create directories mkdir -p /u01/app/oracle/admin/DB11CLN/adump mkdir -p /u01/app/oracle/admin/DB11CLN/pfile mkdir -p /u01/app/oracle/oradata/DB11CLN mkdir -p /u01/app/oracle/fast_recovery_area/DB11CLN 3. create password file for DB11CLN cd $ORACLE_HOME/dbs cp orapwDB11LIN orapwDB11CLN 4. edit TNSNAMES.ORA entry for DB11CLN DB11CLN = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = node32.ansaridba.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DB11CLN) ) ) 5. edit listener.ora file for SID_NAME SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = DB11LIN) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = DB11LIN) ) (SID_DESC = (GLOBAL_DBNAME = DB11CLN) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = DB11CLN) ) ) 6. Start auxiliary insance in nomount export ORACLE_SID=DB11CLN sqlplus / as sysdba startup nomount pfile=$ORACLE_HOME/dbs/initDB11CLN.ORA 7. create rman.txt script run{ DUPLICATE TARGET DATABASE TO DB11CLN FROM ACTIVE DATABASE spfile parameter_value_convert 'DB11LIN','DB11CLN' set db_unique_name='DB11CLN' set db_create_file_dest='/u01/app/oracle/oradata/DB11CLN' set db_recovery_file_dest='/u01/app/oracle/oradata/DB11CLN' set db_file_name_convert='/DB11LIN/','/DB11CLN/' set log_file_name_convert='/DB11LIN/','/DB11CLN/' set log_archive_max_processes='5' ; } 7. Connect RMAN using password file and TNSNAMES entry export ORACLE_SID=DB11CLN rman target sys/[email protected] auxiliary sys/[email protected] @rman.txt
Simplified DBA-010-Exploring dbcontrol, enterpise manager of 11g database
Enterprise Manager is very powerful tool for DBAs. dbconsole service provides EM we have to start dbconsole using emctl utility. to check status of dbconsole $ cd $ORACLE_HOME/bin $ emctl status dbconsole to start dbconsole $ emctl start dbconsole to stop dbconsole $ emctl stop dbconsole
Simplified DBA-007-Creating a Databases on Windows using DBCA
we will create our databases to create 11g database, we have to run DBCA from 11g software home. to reate 12c database, we have to run DBCA from 12c software home. each database needs a listener to receive connection requests from clients. so we have to create a LISTENER first. A listener runs as windows service and needs a port to listen.
Simplified DBA-018-User Privileges
A privilege is a right to execute a particular type of SQL statement or to access another user’s object. The Oracle database enables you to control what the users can and cannot do in the database. Privileges are divided into two categories: System privileges: Each system privilege allows a user to perform a particular database operation or class of database operations. For example, the privilege to create tablespaces is a system privilege. System privileges can be granted by the administrator or by someone who has been given explicit permission to administer the privilege. Object privileges: Object privileges allow a user to perform a particular action on a specific object, such as a table, view, sequence, procedure, function, or package. Without specific permission, users can access only their own objects. Object privileges can be granted by the owner of an object, by the administrator, or by someone who has been explicitly given permission to grant privileges on the object. For example, in our demo ERPUSER will own the tables and other users will require read, write object privileges on ERPUSER tables. Example Connect erpuser/welcome1 Grant insert on myable1 to user1; Grant insert on myable1 to user5 Create tablespace erp_data datafile ‘/u01/app/oradata/db11lin/erpdata01.dbf’ size 100M autoextend on; Create user erpuser identified by welcome1 default tablespace erp_data temporary tablespace temp; Grant connect, resource to erpuser; Create user user1 identified by welcome1 default tablespace erp_data temporary tablespace temp; Create table erpuser.mytable1 ( sno number(5), sname varchar2(20)); We can create a table for any user in this format if we have dba privileges. [owner.tablename] Connect user1/welcome1 Insert into erpuser.mytable1 values(100, ‘by user1’); Commit;
Simplified DBA-026-02-Total Recall with Flashback Data Archive
Flashback Data Archive helps you see old values preserved in FDA.
Simplified DBA-012-What is a SCHEMA in Oracle Database
Before you can create any table in database, you need access and resources in database. Access is allowed to a database user or schema. The term user and schema are used interchangeably. A schema is logical grouping of tables and other objects related to an application. Let say you have two different business applications. One application is used my manufacturing company that connect to database as MFG schema and other is a telecom application that connects to database as TEL schema. You will create two schemas as follows: CREATE USER MFG identified by oracle123 default tablespace users temporary tablespace temp; GRANT connect, resource to MFG; alter user MFG quota unlimited on USERS; CREATE USER TEL identified by oracle123 default tablespace users temporary tablespace temp; GRANT connect, resource to TEL; alter user TEL quota unlimited on USERS; alter user MFG quota unlimited on USERS Connect mfg/oracle123 Create table mfg_data(sno number(1) primary key, sname varchar2(20); Connect tel/oracle123 Create table tel_data(sno number(1) primary key, sname varchar2(20);
Simplified DBA-026-03-Flashback Queries, Version Queries and Transaction Queries
Flashback Queries helps you see past values. It helps you analyse errors.
Simplified DBA-024-Password file
orapwd file=orapwSID password=welcome1 entries=5 what is use of password file.
Simplified DBA-006-Installing database software 11g and 12c on Linux
WE will install software only at this stage. In next demo, we will create listener and create databases
Simplified DBA-004-How to access a shared folder in VirtualBox
Shared folder helps you keep your software outside virtual machine saving space.
Simplified DBA-019-Data Concurrency and Locks
DATA CONCURRENCY More than one user connections ( session ) can access same data. For example user1 and user2 can view same employee information at the same time. But not two users can modify same data in one time. This is maintained by oracle lock mechanism. Before the database allows a session to modify data, the session must first lock the data that is being modified. A lock gives the session exclusive control over the data so that no other transaction can modify the locked data until the lock is released. Transactions can lock individual rows of data, multiple rows, or even entire tables. Oracle Database supports both manual and automatic locking. Automatically acquired locks always choose the lowest possible level of locking to minimize potential conflicts with other transactions. Demo: User1 is modifying an employee. User1 session will acquire a lock on the row. Same time user2 is trying to modify same employee. Since User1 has acquired lock, user2 has to wait until user1 releases the lock. Manual locking is possible. A user can place a lock manually as follows: LOCK TABLE mytable1 IN EXCLUSIVE MODE; With the preceding statement, any other transaction that tries to update a row in the locked table must wait until the transaction that issued the lock request completes. EXCLUSIVE is the strictest lock mode. The following are the other lock modes: ROW SHARE: Permits concurrent access to the locked table but prohibits sessions from locking the entire table for exclusive access ROW EXCLUSIVE: Is the same as ROW SHARE, but also prohibits locking in SHARE mode. The ROW EXCLUSIVE locks are automatically obtained when updating, inserting, or deleting data. ROW EXCLUSIVE locks allow multiple readers and one writer. SHARE: Permits concurrent queries but prohibits updates to the locked table. A SHARE lock is required (and automatically requested) to create an index on a table. However, online index creation requires a ROW SHARE lock that is used when building the index.
Simplified DBA-002-Install Virtual Box and Create a Virtual Machine
In this demo, student will see how to install virtual box first then install extension pack and then we will create a virtual machine.
Simplified DBA-009-Configuring SQL Developer Connections
we can connect to many database from SQL Developer. in this demo we will connect to our four databases. we have two databases on Windows VM we have two databases on Linux
Simplified DBA-011-How to see Structure of a Table in SQL Developer
we will create a table inset some rows explore table structure Table creation script: CREATE TABLE PROFESSIONALS( ID number(5) primary key, NAME varchar2(20), PROFESSION varchar2(30) ); Data entry script: INSERT into PROFESSIONALS values (1001, 'Ansari' , 'Oracle DBA'); INSERT into PROFESSIONALS values (1002, 'Raheemuddin' , 'System Administrator'); INSERT into PROFESSIONALS values (1003, 'Moin' , 'System Engineer'); COMMIT;
Simplified DBA-016- Oracle Networking-LISTENER Management
Oracle clients communicate with oracle databases using Oracle Net Services over TCP/IP. On database server, oracle Net Services has a component called LISTENER that listens connection request on a port. A Listener has a configuration file called listener.ora in $ORACLE_HOME/network/admin folder. Sample listener.ora file:
Simplified DBA-020-1-UNDO Data and Read Consistency
UNDO Data and Read Consistency
Simplified DBA-013-User Session and Background PROCESS
User Session and Connection A connection is communication pathway between user and instance. When a user connects to database, a user process is created, when he gets access to database a server process is created on server side. Both user process and server process communicate over connection and is called a user session. If connection is not established then there is no user session. So, a session is a user current state in database instance
Simplified DBA 026-01-Flashback Vs PITR of RMAN
we will recover a dropped table without rman point in time recovery