DAILYDBADIARIES

 Tablespace consists of one or more data files.  A data file belongs to only one tablespace. Segments, Extents, and Blocks • Segments exist within a tablespace • Segments are made up of a collection of extents • An extents is a collection of logically contiguous data blocks - Cannot span multiple data files • Data blocks are mapped to disk blocks SYSTEM and SYSAUX Tablespaces • The SYSTEM and SYSAUX tablespaces are mandatory tablespaces. • They are created at the time of database creation. • They must be online. • The SYSTEM tablespace is used for core functionality (for example, data dictionary tables). • The auxiliary SYSAUX tablespace is used for additional database components.

 Data files Control files: control files are used to save the database physical structure this includes the database file names and their locations the backup files and the database basic information like creation time and last check  Oracle database instance needs only a single control file to operate. However in most production data bases more than one control files are configured no Oracle database can operate without control files   Control files must be there otherwise the instance goes down. Parameter file: the parameter file is a small file to save the parameter value pairs. The parameter is saved in the file are called installization parameters.  Installization parameters are configuration parameters that controls the instance behaviour. Online redolog files: online redo log files are two or more pre allocated files that store changes to the database as they occur.  The files are used for instance recovery, generating archive redolog files, replicat...

 • An Oracle database server consists of at least one database instance and a database. - A database is a set of files, located on disk, that store data. - An instance is a set of memory structures and processes that manage the database files. • An instance consists of a shared memory area, called the system global area (SGA), and a set of background processes. • For each user connection to the instance, there is a client process. Each client process is associated with its own server process. The server process has its own private session memory, known as the program global area (PGA). System Global Area (SGA) Components • Shared Pool - Library Cache: stores executable SQL and PL/SQL code. - Data Dictionary Cache: holds info about accessed database objects - Server Result Cache: holds result sets (not data blocks). It contains the SQL query result cache and PL/SQL function result cache. • Database buffer cache: the memory area that stores copies of data blocks read from dat...

 • Plan the Database Design • Install Oracle database and Grid Infrastructure Software • Create Oracle Databases • Backup the database • Control database security • Tune Oracle database performance • Implement Database Disaster Recovery (DR) plan • Migrate the on-prem databases to the Cloud • Patching database and grid infrastructure • Upgrade Oracle databases • Create and Manage Oracle clusters (RAC) • Clone databases • Monitor database resources growth and determine future demands • Perform database health check • Oracle database options and licenses • Diagnosing and troubleshooting Oracle databases

Oracle golden gate is a data replicant solution that is seperate from the oracle database.  It is a distinct product that needs to be install and configured separately and request its own licence.  This solution allows from the replication of the subset of data in one database to another subset of data in different database.  One of its most powerful future is to ability to replicate between none.  Oracle database such as replicating some tables in an Oracle database to some tables in SQL server and vice versa.  Another strong feature of this product is its PI directional replication capability.  You just can connect and make changes to data bases on either side whether it is the source or destination Database and golden gate takes care of implementing the changes on each database.  You might wonder about the difference between data card and Golden Gate while data guard also replicates data from the primary database to the standby database, Golden Gate...

 The database connected by the application user is called the primary database when data get this implemented there is another database running at a different site data guard Synchronizes any changes that happen in the primary database to the standby database located   If you are disaster occurs at the site hosting the primary database the service will fail over to the standby  database.  In in this case application users connect to the standby database instead of the primary database  When data guard is implemented by default the standby database cannot be accessed until your disaster occurs.  However there is an option in Oracle database called active data guard with this option application users can connect to the stand by database for read only operations.  This is useful for allowing users who generate reports to access the standby database rather than connecting to the primary database, which would put more load on it.

 RAC: a server for tolerance solution   To address server fault tolerance Oracle offers Oracle database RAC real application cluster options with this option a cluster of nodes collectively represent the database service.   The database service does not stop if one not in the cluster fails this solution is utilised by Enterprises that require high availability applications they cannot afford extended recovery times from backups when a server becomes faulty   Oracle database rack 10 seats between the single instance architecture and the real application cluster   Technical speaking it an active passive cluster solution   Only one not will be at the time if that note goes down for any reason the service fails over to another node and continuous providing it service   This solution is suitable for business that wants to take Advantage of the rak options, but at a lower cost than the full rac license.

Oracle database single instance architecture  Maine database runs on a single machine we referred to this architecture as the Oracle database single instance architecture this setup involves having one Oracle database instance running on the mission serving its clients   The term Oracle database instance simply refers to the combination of memory structure and database process that collectively represent the database service.  However this Architectire has a significant availability risk.  If anything goes wrong with the mission that database becomes UN available.

 • Free for the first year after buying the license • Payable on yearly basis afterwards • Oracle support benefits: - Resolving technical issues and answer technical questions - Obtain patches - Entitled to upgrade the database to the most recent release  Once you purchase an enterprise edition it includes free support from Oracle for 1 year.  After that year client needs to pay for Oracle support services on an annual basis   Oracle support is not just for resolving issues it also needed to download routine patches that should be applied on that data bases.   Additionally if a client continues paying for support each year they are entitled to upgrade their database to the most recent version for free  If you choose not to renew varieties support every year we can still use the database but you wont be able to download and apply patches or upgrade to the latest version

 • User Manager: client is responsible for updating the OS, installing, upgrading, and patching Oracle database software. • Co-Managed: Oracle database software comes pre-installed in the machines. Oracle provides tools to facilitate taking database backups, patching database software, upgrading database software, and providing disaster recovery. • Autonomous: fully automated database service that uses machine- learning (ML) for automatically tuning, scaling, and patching Oracle databases.

 Long Term Support Release - 5 years of Premier support followed by 3 years of Extended Support - Recommended for production databases - Currently is 19c ( PS ends April 30, 2024) - The next one is scheduled to be 23c Innovation Release - 2 year of Premier Support, no Extended Support - Recommended for studying the new features in development environments - Examples: 12c, 18c, and 21c  

 From here 1979 to 1983 Oracle version 3   In the year 1984 Oracle released version 4   In the year 1985 Oracle released version 5   In the year 1988 released version 6 introduced   row locking in this version  In the year 1992 Oracle released the version 7 introduced pl/sql storage programs in this version   In the year 1997 Oracle released the version 8 introduced objects and partitioning in this version   In the year 1999 Oracle released the version 8i internet compounding was introduced in this version   In the year 2001 Oracle released version 9iOracle rac was introduced in this version  In the year 2004 Oracle released version 10g introduced grid computing this version   In the year 2007 Oracle released version 11g introduced diagnosatability and availability    In the year 2013 Oracle released version 12c introduced the multitenancy and Cloud Computing in this version  ...

ORACLE DBA NOTES 3 ORACLE DBA NOTES 2 ORACLE DBA NOTES 1

  We will do patching for every 3 months to fix bugs For every 3 months the new patchversion will be released Command opatch apply Command in oracle support `n-1´ if any new patch releades it will show.        To check the current version of opatch tool Upgrad opatch utility We should take backup from oracle_home full db backup Shut down the database and listner   Shut immediately (to shutdown database) Lstnrctl stop listner_name (to shutdown listner) Apply psu patch on oracle_home Opatch apply Post installation scripts execute Datapatch Dtabase and listner up Startup (to up database) Lsnrctl start listner_name (to start listner) Dba_registery_sqlpatch

 Dr drill / switc over switch back In every company it will be taken for every 3 months (quarterly)                Production At the time of any crash in production data the standby will switch over as production database. Alter database commit to switchover to physical standby with session; Select switchover _ status from v$database; Output: to stand If output: failed destination  (any archives missing) Take full db backup Current mode : read/write End of redo (with the help of win scp send this to stand by in one location or path) After stand by changing to primary use below command to actice  [Alter database activate standby database]                  Stand by Alter database commit to switchover to primary; Select switchover _ status from v$database; Output: to prod If output: failed destination Current mode : to stand (mount) End of redo  Alter database commit to switch over to...

 VVV IMP TOPIC The database should be in sync always LSS - log shipment service LNS - log network service RFS - remote fetching service MRP - managed recovery process ARCH - archive process Production Stand by       DC DR   LNS >>>>>>>>LSS>>>>>>>>>>RFS Redolog files Rfs      Archives redolog files       Lns online archives                                                     Mrp                                               Storage point To see listner working or not fire comman...

 Restoration is one of the major concept and critical one in oracle database With the help of R-man backup we can restore any database at any cost Without full rman backup we cannot restore a database Steps for restoration: We need to complete R-man backup (level 0) at the same time take backup for p-file (p-file = parameter file) Copy the R-man backup pieces in p-file from source server to target server Before restoration make sure same version of binaries hascto be installed We need to change the p-file locations as per the existing database Once the p-file is ready we need to export the enviromental variables Production (DC) standby (DR)    Source Target R man  Back up pieces>> win scp>> location                   Copy>>>>>>>>>>>paste                             ...

 Full db back up level 0 Incremental back up level 1 Full db back up (level 0) has 3 backups Hot backup , cold backup,  consistence backup. Incremental backup (level 1) has 2 backups Level 0 back up it is also known as differential back upit takes less storage Level 1 back up it is also known as cumulative back up it takes more storage After 2012 the compressed back up was introduced  The compressed back up will do the overall data storage/4/2 it is called compressed back up Eg: overall backup/4/2              200GB/4/2 = 25 GB Rman backup: recovery manager backup  A rman backup is used to store the data completely from day one. A rman backup is helpful to recover the database at times of database crash. While we are taking rman backup their multiple files will be backed up in this process Below are the files which will be backed up they are  1.control files 2.redolog files 3.data files 4.temp files (tem...

Export and import will be taken in schema level and table level To export and import in user is called schema level To export and import files or tables is called table level Export and import can be done in schemas, tables and full data base Schema level: To export and import in user is called schema level (Root level query) Expdp directory = directory name, schema = schema name, dump file = dmp log file =. Log compression = all parallel = cpu count/2 Expdp directpry = directory name, schema = schema name, dumpfile = log.compression = all parallel = cpu count/2 Impdp directory = directory name, schema = schema name, dump file = dmp.logfile = .log compression = all parallel = cpu count/2 Table level : To export and import in tables is called table level In table level export we will take only table backup ie. Export of particular table with the help of table export we can store the data on the export dumps. If anything like data misise, we need to import the same table in database. Exp...

To create table space raw/ empty storage or mount point will be given in terabytes   In one table space we can create 1024 data files One data file size is 32GB   In table space it contains tables, segments, extents, blocks, rows, data In this the tables and data are physical remaining all are logical Table space is also logical   The default block size is 8KB We can change the block size manually to 12kb and 16kb   To create table space use this command We should give CRD files location only to create table space   Create tablespace tablespace name datafile ‘/mount point /directory /data file/.table space name.dbf’ size2g;   For eg: employee table Emp id Emp name Emp Sal Emp bon Emp OT 150 Mega star 40,000 2000 8000 786 Power star 56,000 3000 4000 111 Super star 48,000 2600 6000   Command to create table:   Create table emp (emp id num (20), emp sal integer (20), e name varchar 2(20) ); Here integer means numbers, varchar means spelling Semantic ...

A user is also called as schema/owner User can able to connect to database through the ‘custom schema’ All data base objects like views, indexes, tables, etc,… can be created under a schema Customers can be able to connect to the database through the schema/ owner   How to create a user? Create user username  identified by password   How to lock a user? Alter user username  account lock   How to unlock a user? Alter user username account unlock   We have created user but still its not completed, we should grant permissions to the user   Grant connect to username Grant resource to username Grant create session to username Now the permissions was granted now the user is active To delete any user:   drop user username To change password to the user Alter user username  identified by password   To check password expires Dba profile The password expires in 15 days 30 days 45 days 2/6 months /never

The start up mods and shut up mods should be fired on Sql SQL means ‘structure query language’ To connect to sql we should fire environmental variables commands   DB name :   EXPORT ORACLE_SID = db home name Homepath:   EXPORT ORACLE_HOME= /ORACLE-12C/APP/PRODUCT/12C/DBHOME Path:  EXPORT_PATH = $ORACLE_HOME/BiN:$Path To fix environmental variables command     Env |grep ora   Start up mods   Start up no mount       no mount mode       alter data base no mount Start up mount                   mount mode       alter data base mount Start up                                    Open mode  ...

CRD FILES C    control file R    redo log file D    data file   DB link data base link To connect from one database server to another database server is called db link. It is one way communication   We have 3 types of queries Select query     Update query    Delete query   All alter are called update queries All drop are called delete queries   SGA                system global area BP                   back ground process P-mon            program monitor S-mon            system monitor DB writer      data base writer LG WR            log wr...

  Some of the Linux commands which we regularly use in our daily work space mkdir  directory_name     = to create a directory ls -lrt                                 = to view the list of files/directories in any directory vi  file_name                    = to create a new file (or) edit file.  [after creation of file press 'ESC' then                                                'shift key' + :wq! to save the file]   after opening the file click I to                                                         insert the data. cat file_name   ...