Refresh or Clone the Database

1. Check the backup successful or not on that day.
         Ram> list backup;
         Sql> v$backupset;
         $cd  /NSR/appslog
2. Check the database size in Production,file system and space availiability on Dev box.
3. Identify the control file backup in production,check the datafile, temp file and redolog locations.
4. Drop the database in Dev box, if it is already exists.
            a. Put the database in mount with restriction.
               Sql>alter system enable restrict session
            b. drop the database.
5. Start the database in “nomount”
6. Restore the control file ( which you have identify )
        Rman>run
            {
              allocate channel ch1 type sbt=‘tape’;
              allocate channel ch2 type sbt=‘tape’;
              arguments (NSR_SERVER=networker server name,
                         NSR_CLIENT=production server name,
                         NSR_POOL=pool name);
            restore control file from ‘C_DBID_DATE’;
             release ch1;
             release ch2;
          }
7. Open the database in mount, if it is not mount after restoring of control file.
 
           Sql> startup mount
8. Restore the database.
        rman>run
            {
              allocate channel ch1 type sbt=‘tape’;
              allocate channel ch2 type sbt=‘tape’;
              arguments (NSR_SERVER=networker server name,
                         NSR_CLIENT=production server name,
                         NSR_POOL=pool name);
             restore database;
             release ch1;
             release ch2;
          }
9. Restore the archive logs all
         rman>  run
               {
              allocate channel ch1 type sbt=‘tape’;
              allocate channel ch2 type sbt=‘tape’;
              arguments (NSR_SERVER=networker server name,
                         NSR_CLIENT=production server name,
                         NSR_POOL=pool name);
             restore archivelog all;
             release ch1;
             release ch2;
              }
10. Recover database using backup control file untill cancel.
        rman>recover database using backup controlfile untill cancel; 
11.Open the database with resetlogs.
       Sql> alter database open resetlog
12. Check the datafile online or not
        Sql>select file_name,status from dba_data_files;
13. Add the temp file in Dev box.
       Sql>alter tablespace temp add temfile ‘location’ size 10g; 

Linux

What is OS ?

It is a collection of program or software which runs on computer and manages all the hardware resources.
(Or) It is an interface between user and Hardware Resources
It is classified into two types:
i. Single user system
ii.Multi user system
i.Single user system : provides a platform for only one user at a time.
Example : DOS
Multi user system : more than one user can access same system resources at the same time.
Example : Linux , Unix

Features of OS ?

1.Process management
2.Memory management
3.Data management
4.Input & Output management

What is a Linux ?

1.Linux is an operating system.
2.It is one of the flavor of Unix.
3.It is clone of Unix OS.
4.It has been developed for business ,education and personal productivity purpose.
5.Developed in 1991 by Linus Torvalds student from Helsinki University [ finland ].

Features of Linux ?

1.Multi User & Multi Tasking : More than one user can access the system resources &
can perform multiple task at the same time.
2.Open Source: It is an open source code. It can be downloaded from the internet completely for free,
no registration fee.
3.Portability:Independent of any hardware platform.
4.Communication: Exchanging of information or files from one user account to another user account.
5.Security: It provides High security compare to windows.
Two level of securities:
i. System level security : controlled by sys admin.
ii.File level security : controlled by owner of file.

File System

1. It is a method of storing the data in an well organized manner on the disk.
2. It is responsible for storing information on disk and retrieving and updating this information.
Types of File System :
a. Network File System
EX: NFS,DFS
b. Disk File System
EX:ext2,ext3,ext4
FAT32,NTFS

Types of File System

1. In Windows :
FAT32 (File Allocation Table),NTFS (New Technology File System)
2. In Linux :

Ext3,Ext4 (Extended file system)
3. In Unix :
JFS, ZFS (Zeta File System)

Basic Commands in Linux


  1. $Pwd = Where am I ?
  2. cd , cd .. , cd ../.. , cd = Changes working directory.
  3. ls , ls –a , ls -l = Shows the contents of current directory.
  4. cp = To copy file or dir.
  5. mv = To move or rename file or dir.
  6. mkdir = To make or create a directory.
  7. rmdir = To remove a dir.
  8. su = To switch a user.
  9. getconf ‘WORD_BIT’ = Info about bit 32/64.
  10. free –m = Info about RAM memory.
  11. grep = To filter.
  12. cat To create a file .
  13. touch = To create a empty file & multiple file.
  14. vi editor = To create ,append, delete data from file.

*User Management*

1. In linux a user should have account,To log on to a system a user should have username & password for security.
2. In linux, It follows User Private Group (UPG) scheme, with this when ever a user is created with a same name group also
created. This becomes the primary group of that user & a user can have only one primary group.
3. When a user is created, Home directory, Mail account, UID & GID is assign to that user.
4. In Linux, We can create upto 60,000 users, in which
i. Root User ( Admin ) = 0 to 499
ii. Normal User = 500 to 60000
5. [ slm@host ~ ] #
slm = username
host = hostname
~ = present working directory
# = sign of root user
$ = Normal user
6. Commands in user Management
  1. Useradd ( to add a new user )
  2. Usermod (to modify existing user )
  3. Userdel ( to delete a user )
  4. Vi /etc/passwd [to check user info ] Or Cat /etc/passwd
  5. Vi /etc/shadow [ to check user’s passwd info ] Or Cat /etc/shadow
7.How to add a user & how to check it ?
  1. #useradd user1
  2. #Passwd user1
  3. Cat /etc/passwd
    User1:x:500:501: :/u01/home:/bin/bash
    username : user1
    Mask passwd : x
    UID : 500
    GID : 501
    : : - is to add a comment
    Home location : /u01/home
    Default shell : /bin/bash
  4. # passwd user1 [ to assing a password ]
  5. #usermod -c dba user1 [ to add comment ]
  6. #usermod -c ‘’ user1 [ to remove comment]
  7. #usermod -u 510 user1 [ to change UID ]
  8. #usermod -d /u01/home user1 [ to change home location ]
  9. #usermod -L user1 [ to lock a user ]
  10. #usermod -U user1 [ to unlock a user ]
  11. #usermod -s ksh user1 [ to change location of a user ]
  12. #userdel user1 [ to delete a user ]
  13. #userdel -r user1 [ to delete a user from all locations eg:home]
  14. #useradd -g grp1 -m user2 [ to add a user with existing primary group ]
  15. #usermod -G grp1 -m user2 [ to add secondary group to a user ]
  16. #useradd -g grp1 -d /u01/home -m user5
  17. where
    -g is for primary group
    -d is for location
    -m for adding a new user

*Group Management*

1. Group :
          In linux, group is nothing but it is a collection of user whose permissions has to be same.

There are two types of groups are there :
i. Primary group
ii.Secondary group

Primary Group: It is a initial group assign to a user.

Secondary Group: If a user is having a first group [primary group] then you can assign another group that is said to
be secondary group. It is an substitute for primary group.

2. Commands for group management
  1. # groupadd
  2. # groupmod
  3. # groupdel
  4. # gpasswd
  5. Cat /etc/group [ info of group ] (or) Vi /etc/agroup
  6. Vi /etc/gshadow (or) Cat /etc/gshadow [ info of group passwd ]
  7. # groupadd grp1 [ to add a new user ]
  8. # groupmod -g 600 grp1 [to add a new GID ]
  9. # gpasswd grp1 [ to assign a passwd ]
  10. # groupmod -n grpnew grp1 [ to rename a group ]
  11. # gpasswd -M user1,user2,user3 grpnew [ to add multiple user in a existing group ]
  12. # cat /etc/group [ to check ]
  13. #cat /etc/gshadow

*Permissions*

Every file and directory comes with three types of permissions:
1. Read: Lets you view the contents of the file only. In dir level, list the files/sub-dir.
2. Write: Lets you makes the changes of the contents of files [create, modify, or delete files in that directory] . In directory level, it will let you to create files & sub-dir.
3. Execute: Lets you execute (run) the file if the file contains an executable program (script). In directory level, to create a dir.
4. These permission is assign to three different levels/catagories : Owner [user] Group Others
5. Default Permission for a file & directory : File : 644/666
Directory : 755/777
6. This values is subtracted with the umask value that is 022 is default, due to security reasons. You can change the umask value.
7. Read permission is the most basic permission. Having the execute permission without the read permission is of no use—you can’t execute a file if you can’t read it in the first place.
Use the ls –l, ls –al, ll to check permissions 
Eg: $ ls -al

-rwxrwxrwx 1 oracle dba 320 Jan 23 09:00 test.ksh

-rw-r---r- 1 oracle dba 152 Jul 18 13:38 updown.ksh
8. Commands of permissions
  1. Chmod [ to change permission for file&dir ]
  2. Chown [ to change ownership for file&dir ]
  3. Chgrp [ to change group for a file&dir ]
In Chmod,
9. There are two types for assigning permission:
i.Character Method [ Read(r), Write(w), Execute (x) ]
ii.Numeric Method [ r=4, w=2, x=1 ; Total=7 ]

  1. # chmod 755 book(dir)
    Where 
    1st 7 stands for RWX for Owner/User
    2nd 5 stands for RX for group
    3rd  5 stands for RX for others
  2. # chmod u+rwx,g-rx,o-rx book(dir) [permission-755]
  3. # chown user1 book
  4. # chgrp grp1 book

How DML Works

1. Server process send the request of user process to Library Cache.
2. Server process will Handover the Statements to “library Cache” (L.C) of shared pool.
3. A Pre-check will be done for the existence of parsed statement.
4. If the server process doesn’t finds an already parsed statement, it will initiate the phase of Parsing ( First phase in SQL execution).
5. Parsing will be done in three steps.
         a. Dividing the SQL statement into literals.
         b. Checking for syntax error.
         c. Checking for symantic error.
6. The statement will get executed in PGA (using the best execution plan provided by the optimizer).
7. Since DML are changes to the DB redo entries will be Generated.
Redo entry or redo record or change entry (vector ): A single automatic change happen to the DB.
        Ex: if a statement is updating 100 rows 100 redo entries will be generated 
        i.e 100 times DB got modified or changed.
8. Server process will copy the generated redo entries from PGA to log Buffer cache.
9. By taking information from DDC ( data dictionary cache ) server process will check for the data in LRU LIST of database buffer cache.
10.If the data is not found server process will copy both data block and undo block to MRU END of LRU LIST of database buffer cache.
Note: if the data is available in LRU LIST server process will copy only undo block.
11. The old value which is there in data block will be moved to undo block, a new value will be inserted into data block.
12. The modified block are dirty blocks will be moved to write list when they reach LRU end.
Note: every block in database buffer cache holds any of the following:
UN-USED BLOCK : the block is never used.
FREE BLOCK    : the block had been used previously but currently it as free.
PINNED BLOCK  : the block which is currently in use.
DIRTY BLOCK   : the block which got modified.
13. Database writer will write down the contence of write list to corresponding data files.
14. Before DB writer log writer will write down the contence of log buffer cache to redo log files.

How DDL Statement Works ?

1. Server process sends the DDL statement to library cache.

2. Server process will handover the statements to “library Cache” (L.C) of shared pool.

3. A pre-check will be done for the existence of parsed statement.

4. If the server process doesn’t finds an already parsed statement, it will initiate the phase of parsing ( First phase in SQL execution).

5. Parsing will be done in three steps.

       a. Dividing the SQL statement into literals.
       b. Checking for syntax error.
       c. Checking for symantic error.

6. The statement will get executed in PGA (using the best execution plan provided by the optimizer) and redo entries will be generated.

7. Redo entries will be copied to log buffer cache by server process.

8. Server process ( After taking information from DDC ) will search for blocks in database buffer cache. If not found blocks will be copied to MRU END of LRU LIST of database buffer cache.

Note: As all DDL statements are auto commit ( no rollback ) undo blocks will not be copied by server process.

9. The blocks got copied to database buffer cache are “base table” blocks.

10.After modification blocks will become dirty and will be moved WRITE LIST,from where database writer writes to database.

 Note: Before DB writer writes to log writer writes.

How Select Statement Works ?

1. When user process submits the select query, It will be received by the server process.

2. Server process will handover the statements to “library Cache” (L.C) of shared pool.

3. A pre-check will be done for the existence of parsed statement.

4. If the server process doesn’t finds an already parsed statement, it will initiate the phase of parsing ( First phase in SQL execution).

5. Parsing will be done in three steps.

       a. Dividing the SQL statement into literals.
       b. Checking for syntax error.
       c. Checking for symantic error.

6. Server process will perform symantic checking (checking whether the table is existing or not, checking for necessary priviliges etc.)

7. Server process will checking using dictionary information in data dictionary cache (DDC). Note: If the information is not availiable server process will copy that from database.

8. Optimizer ( Brain of oracle) will generate many execution plans ( a plan specifies how fast SQL statement get executed and choose the best one based on time and cost. Note: Time represents the last response time and cost represents least CPU utilization.

9. The statement will get executed in PGA (using the best execution plan provided by the optimizer) in three spets.

    a. Conversion of charaters into ASCII values.
    b. Compilation.
    c. Running.

Note: oracle will performs compilation and run its internal algorithms.

10. After execution server process will collect some more Dictionary information from DDC (data Dictionary cache).

11. Server process will start searching for data from LRU END (LRU means Least recently used) in LRU LIST of database buffer cache.

12. The search will continue till it reaches MRU end or the data is found.

Note: If the data is not availiable in LRU LIST then server process will copy the data from database and places a copy at MRU END of LRU LIST of Database buffer Cache.