Oracle Enterprise Manager

Configuring Oracle Enterprise Manager for the first time

export ORACLE_SID = prod

sqlplus / as sysdba

select username, account_status from dba_users order by 1;

From the listed users we need SYSMAN and DBSNMP users for OEM functioning.

select username, account_status from dba_users 

where username in('SYSMAN','DBSNMP');

Both accounts are locked.

To unlock them and reset their password:
alter user sysman identified by manager account unlock;
alter user dbsnmp identified by manager account unlock;

configure a listener to use with OEM:

netmgr
listener name list_prod
port 1530
lsnrctl start list_prod

$emca -config dbcontrol db -repose create
database SID: prod
listener port number: 1530
listener home:
pwd for sys user:
pwd for dbsnmp user:
pwd for sysman user:
yes

once the configuration completes successfully 
note down the database control URL
Open it in firefox browser in linux.

this all for the first time configuration.  once done to use OEM we need the following:
1. database must be started
2. listener must be started
3. emctl service must be started

sqlplus / as sysdba
startup;

lsnrctl start list_prod 

emctl start dbconsole

to start oem in chrome or firefox
https://147.43.0.13:1158/em/

here 1158 port number is for prod database. we get a diff port number for each of the databases which we configure with OEM.

Cloning Vs Db Refresh

What is a Database Clone?

* A database clone is an activity / procedure which is performed by every DBA on regular basis or when there is a requirement or request to do so from the different departments i.e. Test / Development teams.

* Cloning is nothing but creating a copy of production system in to a test or development environment. i.e. Having an exact image of production database in test area.

* Cloning is a procedure for preparing and creating a test or development servers with the copy of Oracle production database for testing upgrades, migrating an existing system to new hardware.

* A cloning process includes a copy of Oracle Home (Directories and Binaries) backup and Database (Database related files) backup to prepare the instance on another server.

* Though, it is possible to clone a database on the same server, Oracle doesn’t suggest to clone a database on the same server, where the production database is running.


What is a Database Refresh?

* A Database Refresh is also referred to as a database clone. However, we don’t clone Oracle Home rather we clone the Database as refresh.

* Refreshing a database is something like applying the changes or updates of production database to the database where the database is already cloned. i.e. let’s say you have cloned a database a month back, and now you are asked for doing refresh of a database, then you will perform the backup of database and prepare the clone the instance again on test server. This is nothing but refreshing.

* Refreshing of a particular table, group of tables, schema, or tablespace will be done using traditional export / import, transportable Tablespaces, or data pump methods.

* When an Oracle patch is applied on Production System, or in doubt, you have to prepare and clone the database again with the copy of Oracle Home (Directories and Binaries) Backup and Database (Database related files) Backup to prepare the instance.

* The difference between Cloning and Refreshing is that cloning process includes Oracle Home and database Clone; where as Refreshing process only includes database clone.

* If seen, the words, Clone and Refresh are used interchangeably for the sake of convenient.



When and why we Clone a Database?

* Generally production (PROD) database is cloned for various reasons and needs i.e. for something to be tested or something to be developed later those to be moved to production.

* It’s normal and quite common thing is that whenever there is any change or update to be performed and do not know the impact or effect after applying it on production (PROD), it’s required to be applied and tested on *NON* production database first (TEST or DEV), after the confirmation of change success, given by the users, then the changes will be moved to production.

* A Cloned test instance (TEST) for testing team/environment is exclusively used for testing the changes or issues which will be come severe on Production. Oracle Support gives the solution as fix when there is an issue in the database, so this fix needs to perform or apply on test/development databases.

* A Cloned development instance (DEV) for development team/environment is used for developing the new changes and then deploying the same on Production.

* A Cloned patch instance is used for patching to know the impact and the time required to apply the same on Production.

Linux Cron Utility

An experienced Linux sysadmin knows the importance of running the routine maintenance jobs in the background automatically.

Linux Cron utility is an effective way to schedule a routine background job at a specific time and/or day on an on-going basis.

Linux Crontab Format

MIN HOUR DOM MON DOW CMD

MIN = Minute field = 0 to 59
HOUR= Hour field = 0 to 23
DOM = Day of Month = 1-31
MON = Month field = 1-12
DOW = Day of Week = 0-6
CMD = Command = Any command to be executed.

crontab -e to edit crontab file
crontab -l to display crontab file 
crontab -r to remove crontab file
crontab -v to display the last time you edited your crontab file.
crontab -u used in conjunction with other options, this option allows you to modify or view the crontab file of user, when available, only administrator can use this option.

Example:-
vi task.sh
cal >> /home/oracle/test1.txt
:wq

make sure script has all the permissions

chmod 777 task.sh

we are going to run the task every minute

now go to the crontab

$crontab -e

* * * * * /home/oracle/task.sh

:wq

1. Scheduling a Job For a Specific Time

30 08 10 06 * /home/ramesh/full-backup

8-30 am
10th day
6th month (june)
* every day of the week

2. Schedule a Job For More Than One Instance (e.g. Twice a Day)

The following script take an incremental backup twice a day every day.

00 11,16 * * * /home/oracle/incremental-backup

00 = 0th minute
11,16 = 11 am, 4pm
* = every day
* = every month
* = every day of the week

3. Schedule a Job for Specific Range of Time (e.g. Only on Weekdays)

Cron Job everyday during working hours

00 09-18 * * * /home/ramesh/bin/check-db-status

00 = 0th minute
09-18 = 9am, 10am, 11am, 12am, 1pm, 2pm, 3pm, 4pm, 5pm, 6pm
* = every day
* = every month
* = every day of the week


00 09-18 * * 1-5 /home/ramesh/bin/check-db-status

00 = 0th minute
09-18 = 9am, 10am, 11am, 12am, 1pm, 2pm, 3pm, 4pm, 5pm, 6pm
* = every day
* = every month
1-5 = mon, tue, wed, thu and fri (working days)

4. How to View Crontab Entries?

$crontab -l

To view Root user Crontab entries


# crontab -l

To view other linux user's crontab entries

{username} -l

[root@dba ~]# crontab -u oracle -l
* * * * * /home/oracle/task.sh

5. How to Edit Crontab Entries?


$crontab -e

Edit Root Crontab entries

root@dba-db# crontab -e

To edit crontab entries of other Linux users, login to root and use -u 


root@dba-db# crontab -u oracle -e

6. Schedule a Job for Every Minute Using Crontab.

* * * * * CMD


when we specify */5 in minute field means every 5 minutes

7. Schedule a Background Cron Job For Every 10 Minutes.

*/10 * * * * /home/oracle/check-disk-space

Instead of specifying values in the 5 fields, we can specify it using a single 

keyword as mentioned below.

There are special cases in which instead of the above 5 fields you can use @ 

followed by a keyword — such as reboot, midnight, yearly, hourly.

keyword equivalent

@yearly 0 0 1 1 *

@daily 0 0 * * *

@hourly 0 * * * *

@reboot run at startup

8. Schedule a Job For First Minute of Every Year using @yearly

@yearly /home/oracle/red-hat/bin/annual-maintenance

9. Schedule a Cron Job Beginning of Every Month using @monthly

@monthly /home/oracle/suse/bin/tape-backup

10. Schedule a Background Job Every Day using @daily

@daily /home/oracle/arch-linux/bin/cleanup-logs "day started"

11. How to Execute a Linux Command After Every Reboot using @reboot?

@reboot CMD

Delete Data from Large Table

Bulk Delete

The rule to decide when to use a bulk delete: "Use it when you cannot do the delete in a single SQL statement".

Here, you probably had a table with more number of indexes and deleting just does a lot of work.  Maintaining the index structures for 300,000 deletes can be "cumbersome". Additionally there are lots of UNDO and REDO generated.

If 300,000 was a large percentage of the table, it is sometimes better to:

create table temp nologging as 
select * from t where id not in ( select id from a );

(keep the rows you want)

index temp (unrecoverable, in parrallel );
grant on temp (as you had for t);
drop table t;

rename temp to t;



You quite simply have too much data for it to be deleted quickly. No matter what method you use, it will take a significant amount of time. If you are confident of your code, you can also disable constraints while pruning your data, which will save some time.

It might be easiest to create a new table with the rows you want to keep - then drop the old one but whatever you do will take a large amount of time.

Some helpful hints include:

1. Use partitioning:  The fastest way to do a mass delete is to drop an Oracle partition.

2. Tune the delete subquery:  Many Oracle deletes use a where clause subquery and optimizing the subquery will improve the SQL delete speed.

3. Use bulk deletes:  Oracle PL/SQL has a bulk delete operator that often is faster than a standard SQL delete.

4. Drop indexes & constraints:  If you are tuning a delete in a nighttime batch job, consider dropping the indexes and rebuilding them after the delete job is completed.

5. Small pctused:  For tuning mass deletes you can reduce freelist overhead by setting Oracle to only re-add a block to the freelists when the block is dead empty by setting a low value for pctused.

6. Parallelize the delete job:  You can run massive delete in parallel with the parallel hint.  If you have 36 processors, the full-scan can run 35 times faster (cpu_count-1)

7. Consider NOARCHIVELOG:  Take a full backup first and bounce the database into NOLOGGING mode for the delete and bounce it again after, into ARCHIVELOG mode.

8. Use CTAS:  Another option you can try would be to create a new table using CTAS(Create Table As Select) where the select statement filters out the rows that you want to delete. Then do a rename of the original followed by a rename of the new table and transfer constraints and indexes.


Options for deletion

1. Option
Use a simple delete command with WHERE condition. Make sure where clause brings limited number of rows and commit with each deletion.

ex:
delete from mytab where year = '2008';
commit;   

delete from mytab where year = '2009'; 

commit;   

2. Option
CTAS (Create Table As Select)
make sure we have tables constraint and index scripts. We can get through dbms_metadata.get_ddl.

Create the table into another tablespace with required number of rows. Then drop the original table. rename the new table with old one.

create table new_mytab
as
select * from mytab where year = '2012'
tablespace new_tablespace;   

rename mytab to old_mytab; 

rename new_mytab to mytab   

add constraints and indexes

3. Option
Delete and copy back into an existing tablespace.

STEP 1 – Copy the table using a WHERE clause to delete the rows:

create table new_mytab 
as
select * from mytab where year = '2012' 
tablespace new_tablespace;   

STEP 2 – truncate the original table:

truncate mytab; 

STEP 3 – Copy-back the rows into the original table definition.  Please note that this step may required a dedicated rollback or undo segment:

alter session set rollback_segment = 'HUGE_RBS';   

insert into mytab 
select * from new_mytab;     

STEP 4:  drop table old_mytab;


In sum, a create table as select (CTAS) approach can be faster than a vanilla delete when the majority of the table rows are being deleted.  CTAS is fast because CTAS can be parallelized, and the required full-scan can be run with parallel read processes such that on a 64 CPU server, the CTAS will scan the table 63 times faster.  CTAS can be used with the NOLOGGING option.



Factors affecting massive deletes

1. Is the target table partitioned ?

    its easier to delete individual partitions

2. Can you reorganize the table after the delete to remove fragmentation?

3. What percentage of the table will be deleted ?

    If the deleted records are more we can create a table using CTAS with required rows and drop the other old table. and rename the new table back to original name.

In cases where you are deleting more than 30-50% of the rows in a very large table it is faster to use CTAS to delete from a table than to do a vanilla delete and a reorganization of the table blocks and a rebuild of the constraints and indexes.

4. Do you want to release the space consumed by the deleted rows? 


if you want to released the space back onto the tablespace then you will need to reorganize the table.




dbms_metadata

In Oracle we have the dbms_metadata utility to display DDL directly from the data dictionary.  Using this powerful utility, we can punch individual objects or an entire schema.

we need to execute dbms_metadata.get_ddl


To punch off all table and indexes for the EMP table, we execute dbms_metadata.get_ddl

set echo off;
Set pages 999;
set long 90000;

spool ddl_list.sql

select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;

select dbms_metadata.get_ddl('INDEX','PK_DEPT','SCOTT') from dual;

spool off;



to get the whole schema we can modify the above script

set pagesize 0
set long 90000
set feedback off
set echo off 

spool scott_schema.sql 

connect scott/tiger;

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
     FROM USER_TABLES u;

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
     FROM USER_INDEXES u;

spool off;

Linux

iostat = to monitor i/o at disks

mpstat = microprocessor activity

vmstat = virtual memory

netstat = network activity

swapon -s = swap memory usage

top = to view most resource consuming processes

free = to view free space available


$head
is used to display top lines of the file
$head -10 filename

$tail 
to see the last lines of a file

$tail -20 filename

$cal

is used to display calendar

$cal -3 --> displays previous, current and next months calendar

Some usefull files:

/etc/group -> group settings

/etc/hosts -> hostname information

/etc/exports -> it states how partitions are mounted and shared with other Linux/Unix systems.

/etc/fstab -> this file automatically mounts filesystems that are spread across multiple drives

/etc/inittab -> describes which processes are started at bootup or at different runlevels.

/etc/passwd -> contains user information

/etc/shells -> contains the names of all the shells installed on the system

/etc/ftpusers -> contains the login names of users who are not allowed to log in by way of FTP

/etc/shadow -> it contains encrypted passwords are stored in /etc/shadow

/etc/profile -> This file contains settings and global startup info the bash shell

/etc/crontab -> Lists commands and times to run them for the cron deamon

/var/log/messages -> The main system message log file

/var/spool/mail -> where mailboxes are usually stored

/etc/resolv.conf -> Configures the name resolver, specifying the address of your name server and your domain name.

/etc/sysconfig/network -> Defines a network interface

/proc/version -> The kernel version

/etc/redhat-release -> Includes one line stating the Red Hat release number and name

vi EDITOR

vi editor is used to make new text files, scripting files or modifying contents of existing files.

modes of vi editor: command mode, execution mode

command mode:

i - to start insertion from current cursor position

I - to start editing from beginning of line

a - to append contents to right side of your current cursor position

A - to append contents to the end of the current cursor line

r - to replace a char from current cursor position

R - to replace a whole word from current cursor position

x - to remove the char from current cursor position

dd - to remove or to delete a line

2dd - to remove 2 lines from current cursor position

yy - to copy the line of current cursor position

2yy - to copy 2 lines from current cursor position

p - to paste the copied content to after cursor position

P - to paste the content above the cursor line

u - to undo previous operations

dgg - to remove or delete above lines including current cursor line till beginning of file

dG - to remove or delete below lines including current cursor line till end of file

l - to move cursor to character by character at a time

h - to move cursor back char by char

k - to move cursor to above line

j - to go to next line

H - to go to end of the file

G - to go to beginning of file

Execution mode

esc - to go to execution mode

:w - to save changes in the file

:q - to quit from the file

:wq - to save and quit

:x - to save and quit

:wq! - Forcibly save changes in the file and quit

:%s/oldtext/newtext/g - to replace all oldtext with newtext in the file

:se no - to display line number

:no se - to hide line number

Partitioning with fdisk

fdisk -l  - to view all available partitions

fdisk -l /dev/sda - view partitions of a specific hard disk 

fdisk -s /dev/sda - to view the size of an existing partition

mkfs.ext3 /dev/sda - after partition is created, format it using the mkfs command

partprobe /dev/sda - It will update in kernel

mount /dev/sda /u03 - mount on a particular directory

Save the /etc/fstab file.(To make partition permanent)

/dev/sda /u03 ext3 defaults 0 0

fsck /dev/sda2 - To repair a corrupted file system.

Oracle Database

Oracle SID and DB name

ORACLE_SID is used to distinguish this instance from other Oracle Database
instances that you may create later and run concurrently on the same host computer. 

The maximum number of characters for ORACLE_SID is 12, and only letters and
numeric digits are permitted. On some platforms, the SID is case-sensitive.

It is common practice to set the SID to be equal to the database
name. The maximum number of characters for the database name is
eight.

ORACLE_SID max characters = 12
db_name max characters = 8
------------------------------------------

select * from v$controlfile_record_section;

displays information about the control file record sections

For more details go to the below link:
https://docs.oracle.com/cd/B12037_01/server.101/b10755/dynviews_1050.htm


As soon as we created tablespace, the space will be occupied in full from the hard disk.

A tablespace managing extent info into its header is called Locally Managed Tablespace (LMT).  LMT is also called as self managing tablespace.

A tablespace managing extent info in data dictionary is called Dictionary Managed Tablespace (DMT).

Checkpoint in one word is called synchronization.

Database in mount mode means entire database is offline.

If datafile is empty we can decrease its size.

Swap is known as performance killer.


To view total db size:

select sum(bytes)/1024/1024 "mb" from dba_data_files;


To view free space by tablespace:

select tablespace_name, sum(bytes)/1024/1024 "mb"
from dba_free_space
group by tablespace_name;


How many extents are already allocated to a table ?

select segment_name, segment_type, block_id, bytes, blocks
from dba_extents
where segment_name = 'EMP'
and segment_type = 'TABLE';

select extent_id, block_id, blocks
from dba_extents
where segment_name='EMP';
0 144      8

This means starting from block id 144 its using total 8 blocks.


To see exactly which row is stored in which block:

select empno,
dbms_rowid.rowid_relative_fno(rowid) file#,
dbms_rowid.rowid_block_number(rowid) block#
from scott.emp;

     EMPNO      FILE#     BLOCK#
---------- ---------- ----------
      7369          4        151
      7499          4        151
      7521          4        151
      7566          4        151
      7654          4        151
      7698          4        151
      7782          4        151
      7788          4        151
      7839          4        151
      7844          4        151
      7876          4        151
      7900          4        151
      7902          4        151
      7934          4        151

rlwrap

rlwrap = ReadLine Wrapper

One nice features of using SQL*Plus in Windows is that you can use the “up arrow” and "down arrow” keys to display the command line history for all SQL*Plus commands.

It's also possible to do this in Linux and UNIX (AIX, Solaris, HP/UX) with the freeware rlwrap utility.

The readline wrapper (rlwrap) utility uses the GNU readline library.  Hence, rlwrap is not Oracle-centric, it’s a standard OS utility available for all flavors of UNIX, Linux and even Windows.  The rlwrap software installs easily on UNIX/Linux with these standard GNU unzip and make commands:

We can download rlwrap from the below link:

http://utopia.knoware.nl/~hlub/uck/rlwrap/

save the .gz file into /tmp folder
$cd /tmp

gunzip rlwrap*.gz
tar -xvf rlwrap*.tar
cd rlwrap*
./configure
make
make check
make install

Now to use the rlwrap utility:

$rlwrap sqlplus / as sysdba

now up arrow and down arrow link work.


If we want to set this concept permanently in bash_profile, do the following:

vi /home/oracle/.bash_profile
alias rlsqlplus='rlwrap sqlplus / as sysdba'

now to execute the above full command just type:

$rlsqlplus