DAILYDBADIARIES

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   ...