Loading...

Follow DBA Genesis on Feedspot


Valid
or
Continue with Google
Continue with Facebook

This blog post is a quick summary of How to become a DBA course - A 100% free course which speaks about everything you need to become an Oracle DBA.

Learning SQL is just not enough to become an Oracle DBA. You need much more skills and learn other things to crack your first job interview. In this blog post, I would like to talk about 15 things you must know as a Junior (Fresher) DBA even before you would like to start your career as database administrator.

1. Basic Linux Commands and Scripting

98% of the servers in all the MNCs out there use Unix or Linux operating system (or some flavor of Unix of Linux OS). It is obvious that the Oracle database you will be working on would also be hosted on some Linux server. And, not having good hands-on knowledge on Linux will be one of the biggest road blocks in your path to become a DBA.

We have Linux Administration course that covers all the aspects of Linux commands right from the Linux basics and gradually providing an overview of shell scripting in Linux. The course further advances by equipping you with Linux networking.

2. Basic Virtualization Knowledge

Now that you know the importance of Linux operating system, the next big question is how to practice it. In order to learn Linux, you need to install it on your laptop (or desktop) to master it. In order to do so, you must install virtualization software like VMWare or Oracle Virtual Box.

A virtualization software allows you to run another operating system on top of your Windows system. This will allow you to run both Windows and Linux operating system on one PC.

I have published Foundation for DBA course, a 100% free course (I mean it ;), which will help you setup your own Linux machine. Go do it now!

3. SQL Fundamentals Knowledge

SQL is the standard language to read and write data from Oracle database. To be very frank, SQL is the standard language to read and write data from any database. The syntax might be different for different databases but overall SQL query structure will be same.

So why do you need to learn SQL in order to become a database administrator. Good question! The thing is when you work with application team, they might need your help in order to write more efficient queries and quick response time.

As a DBA, you must be very good at SQL language (not because application team will approach you ;) because you will be the owner of the database server. You will be responsible for analyzing lot SQL queries which are run inside the database.

4. Knowledge on Database Normalization

Database normalization is a technique to organize data in the form of tables (rows and columns) or most appropriately relational tables (coz we work on RDBMS). This involves taking raw data and convert it into meaningful tables. The process involves you to push data from Three Normal Forms (there are more but three is mostly enough).

One of the beauty of database normalization is, you become familiar with unorganized data. You will be able to identify data dependency, column dependency, table dependency and also remove unnecessary columns or rows.

Pro Tip: You must an expert when it comes to database normalization because it helps you to work with database architect in order to design table structures which are easy to query with less response time.

5. Overview of PL/SQL language

So the application or development team ran a PL/SQL code which has some issues, they will straight away contact DBA team. And! you cannot put blank face saying "I just love SQL and have no idea about PL/SQL" :)

Its a good idea to have an overview of PL/SQL language. No really in depth because it is used by Oracle developers to write programs. As a DBA, just having an understanding of how PL/SQL works and basic PL/SQL programs should be good enough.

6. Installing and Configuring Oracle Database

As a DBA, you are responsible for setting up databases on different servers. This includes performing operating system level pre-requisites, downloading proper database software version, installing oracle software, create database, configure network and much more.

Installing and configuring Oracle database is one of the primary jobs of a DBA.

7. Excellent Understanding of Oracle Database Architecture

When I take any DBA interview, I always look for strong foundation in database architecture. Having a perfect understanding of Oracle database architecture will give mileage to your DBA career. It allows you to understand Oracle performance tuning concepts easily.

Do you want to master Oracle database administration? Come, join me and master Oracle database architecture along with real-time database administration course. You get to learn database administration within a month!

8. How to Plan Your Database Creation

I know when you practice database creation in your lab machine, you fire DBACA and boom - database created. But its not the case in real time. You need to check many things even before you can think of firing DBCA to create database.

Check out my blog post on how to plan your database creation here.

9. Database Startup and Shutdown Modes

If you are not yet a DBA, there are different stages database passes through when you start and shutdown the database. Each stage or phase has a significant use. For example, you put database in Mount mode to perform major administrative tasks.

When you perform database recovery, you will realize the importance of each database file and different database startup modes. Like, in order to take your database to no-mount stage, you need to have SPFILE. To take your database to Mount stage, you need control files and it goes on.

Its very important to know every details in which database starts and how database shutdown happens.

10. Database Health Checkup and Files Multiplexing

With so many production databases in any given environment, its very critical to check database health. Whenever there is some error inside database, you troubleshoot the issue and then perform a quick database health checkup. This allows you to confirm that database is running fine and its ready to accept user connections.

Some of the database health checkup includes:

  • Checking database alert log for ORA errors
  • Checking database open mode and listener status
  • Checking tablespace utilization
  • Checking latest database backup

and few more tasks.

11. Capacity planning & tablespace management

A DBA must maintain adequate space in database tablespaces in order to have smooth functioning. All the tablespaces must be below threshold value and they all must be online (In case if no tablespace is put offline).

Check out one of my blog posts on Query to Check Tablespace Utilization.

I have also published one YouTube video on Tablespace Administration - How to work with Tablespaces in Real-Time?

12. User, profile and role management

Whenever application team adds new members in their team, they might reach out DBA to add new uses inside the database. A database user has permission to access the database server over a network and run queries inside the database.

Different database users have different permissions. For example, a reporting user will only have SELECT permissions on tables. Same way different users will have different access permissions over tables and other objects inside the database.

A DBA is responsible for creating new users, adding or modifying profiles, granting permissions, creating roles and assigning it to users, dropping users and lot of other user management tasks.

13. Database networking

Database networking is key to access database server from client machine. An application connects to database server over a network to access the database. Oracle listener runs on database server where all the client's connections land first and then handed over to the database.

Database networking involves configuring listener, configuring TNS entries, checking remote database connectivity, registering database with the listener and making sure application is able to connect to the database.

14. Data Export and Import between databases

One of the constant activities that are performed by DBA is export and import of data. In many projects, DBA has to perform exports from production database and import into the test or development databases. The activity frequency is generally weekly but in some projects, it is done daily as well.

There are different database utilities which are used to perform table/schema/entire database export and import. You even use RMAN refresh to perform entire database cloning or duplication.

15. Database backup and recovery

I cannot stress on how important database backup and recovery is. Understand this, if you are not able to recover a database after crash, then there is no point in being a DBA :P

Database backups include logical backups and physical backups. The logical backups are nothing but backups of database objects which you take via Export Import or Data Pump utility. The physical backups include cold backup, hot backup and RMAN backups.

Database recovery is where a DBA must be very good at. There are 1000 of database crash scenarios but you do not have to learn all the scenario as you will not encounter all those in your job (until you are most unlucky guy :). You must know the recover concepts well so that in any type of crash, you are still able to bring up the database using backups you have.

Quick Summary

All in all, in order for you to become a DBA, you must have proper hands on experience on all the 15 things mentioned above. You can even checkout my Oracle DBA for Beginners course where you learn to become a database administrator within a month !!

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Database creation process involves several operating system files to work together. You create database only once and later on keep on adding more data files.

Before you can simply go ahead and start creating your database in real-time, you must know the database planning tasks.

Recommended Database Planning Tasks
  1. Plan the database tables and indexes and estimate the amount of space they will require.
  2. Plan the layout of the underlying operating system files your database will comprise.
    • Proper distribution of files can improve database performance dramatically by distributing the I/O during file access. Example, put redo log files and data files on separate physical HDD to improve I/O and response time.
  3. Select the global database name. Create the global database name by setting both the DB_NAME and DB_DOMAIN initialization parameters.
  4. Familiarize yourself with the initialization parameters contained in the initialization parameter file
  5. Select the database character set
  6. Consider which time zones your database must support
  7. Select the standard database block size. This is specified at database creation by the DB_BLOCK_SIZE initialization parameter and cannot be changed after the database is created
  8. Determine the appropriate initial sizing for the SYSAUX tablespace
  9. Plan to use a default tablespace for non-SYSTEM users to prevent inadvertently saving database objects in the SYSTEM tablespace
  10. Plan to use an undo tablespace to manage your undo data
  11. Develop a backup and recovery strategy to protect the database from failure

Prepare to create the database by research and careful planning.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 


One of the best part about being an Oracle DBA is - Database ownership! As a DBA, you actually own the database. That being said, one big question arises - which company would handover critical databases to a fresher?

To be honest with you, there are not enough opportunities for fresher DBAs. But, do not worry. I will give you 5 ideas from my experience to get a job as a fresher DBA!

Understand this, if you own a company, will you hand over your company database in the hands of inexperienced DBAs? Database being the backbone of a company, you as a fresher needs to prove yourself to be trusted and hired → I will talk more about it in next lecture but before that, let me give my 5 ideas to get a job as a fresher DBA:

FIRST IDEA - CAMPUS RECRUITMENTS

If you are into your final SEM or final year of college and if you have campus recruitments, it's a very smooth entry for freshers to become a DBA. Based on your aptitude, interest and attitude, you will easily be hired as a software trainee.

Once you join the company, you can always share your interest in database with your training manager. The company will invest in you to learn database administration and absorbe you in one of their projects

So, for people who are about to graduate and have campus recruitments in their college, this should be your first place to start as a fresher DBA

SECOND IDEA - APPLY IN STARTUPS

21st century is all about rapid innovation and the new age entrepreneurs are coming up with innovative solutions to simplify our problems. Entrepreneurs are constantly looking for fresh talent out of college and it is very easy for them to mould fresh candidates into their startup culture.

One more reason why entrepreneurs would choose fresher DBAs is → they are constantly looking at minimizing the operational costs. Its very easy for entrepreneurs to hire fresher DBAs and train fresher DBAs for low salaries when compared to experienced DBAs.

So, as a fresher DBA, the second place you can try to apply should be startups

THIRD IDEA - GO FOR DBA INTERNSHIPS

Another way to become a DBA is to apply for DBA internships. There are lot of companies including startups which offer internships. The biggest benefit that you get from internships is → you get real-time experience along with internship certificate. Once you spend good enough time in an internship, you can apply for experienced DBA position depending upon your internship duration.

So, as a fresher DBA, you can apply for internships and then use the internship as a launch pad to apply for experienced DBA job!

FOURTH IDEA - ASK YOUR DBA FRIENDS TO REFER YOU

Check your phonebook, facebook, linkedin friends who are currently working in MNC. All the MNCs have an employee referral program. Also, the company employees get to know about open positions much earlier than job sites.

Connect to all such friends, send in your resume and ask them to keep an eye on fresher DBA openings.

FIFTH IDEA - KEEP LEARNING AND KEEP HUNTING

Once you are ready for fresher DBA interviews, you won’t get job immediately. Hence, the best way is to still keep on learning new topics in Oracle Database administration. Try to post your resume on as many job sites as possible.

Ultimately, I gave you my 5 ideas which you can try to get a job as a fresher DBA. Join my How to become a DBA course which is 100% free and it talks about everything you need to know before starting Oracle career!


Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In your environment, you might require to migrate ASM disks from one storage to another storage. The activity is very simple and straight forward. You can follow bellow procedure in order to migrate ASM disks without any downtime.

High level disks migration steps:

  • Get the new disks mounted on RAC nodes - from new SAN storage
  • Create ASM disks from the newly mounted disks
  • Add the new disk to existing diskgroup - where you want to replace old ASM disks
  • Wait for re-balance to complete
  • Drop the old disk from the diskgroup
  • Wait for re-balance to complete
  • Done!

Let us perform this activity on a test 11gR2 RAC database with 2 nodes. Below are the 3 disks allocated on both RAC nodes which are further allocated to CRS, DATA and FRA diskgroups

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1         915     7349706   83  Linux
/dev/sdb2             916        1830     7349737+  83  Linux
/dev/sdb3            1831        2610     6265350   83  Linux

Below are the diskgroup details

DISKGROUP  DISKNAME       TOTAL_MB    USED_MB    FREE_MB PATH                           HEADER_STATU
---------- ------------ ---------- ---------- ---------- ------------------------------ ------------
CRS        CRS_0000           7177        396       6781 /dev/oracleasm/disks/CRS1      MEMBER
DATA       DATA_0000          7177       2009       5168 /dev/oracleasm/disks/DATA1     MEMBER
FRA        FRA_0000           6118        366       5752 /dev/oracleasm/disks/FRA1      MEMBER

We are going to replace the DATA1 disk under DATA diskgroup with DATA2 disk. This new disk we have allocated via a new storage. Let us first create the ASM disk

[root@oraracn1 ~]# oracleasm createdisk DATA2 /dev/sdc1
Writing disk header: done
Instantiating disk: done

Connect to ASM via sqlplus and add the new diskstring. In our example, the new disk location is same as old disks, so no need to add. In case you have different disk path, add it to ASM_DISKSTRING parameter. Keep both old and new paths.

sqlplus / as sysasm
SQL> alter system set asm_disktring = '/dev/oracleasm/disks/*' , '/dev/new_loc/*';
SQL> select path from v$asm_disk;

Check the ASM disks details via below query. The new disk status must be PROVISIONED

set pages 40000 lines 120
col PATH for a30
select DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,
PATH FROM V$ASM_DISK;
DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE    PATH
----------- ------- ------------ ------- -------- ------------------------------
          0 CLOSED  PROVISIONED  ONLINE  NORMAL   /dev/oracleasm/disks/DATA2
          0 CACHED  MEMBER       ONLINE  NORMAL   /dev/oracleasm/disks/FRA1
          0 CACHED  MEMBER       ONLINE  NORMAL   /dev/oracleasm/disks/DATA1
          0 CACHED  MEMBER       ONLINE  NORMAL   /dev/oracleasm/disks/CRS1

We will now add DATA2 to DATA diskgroup and later remove DATA1 disk.

SQL> alter diskgroup DATA add disk '/dev/oracleasm/disks/DATA2';
OR
SQL> alter diskgroup DATA add disk '/dev/oracleasm/disks/DATA2' rebalance power 20;
Diskgroup altered.

Wait for re-balance operation to complete

SQL> Select operation, state, est_work, est_minutes from v$asm_operation; 
OPERA STAT   EST_WORK EST_MINUTES
----- ---- ---------- -----------
REBAL RUN        1175           0

Once re-balance operation is completed, check the disk details via below query

set lines 999;
col diskgroup for a10
col diskname for a12
col path for a30
select a.name DiskGroup,b.name DiskName, b.total_mb, (b.total_mb-b.free_mb) Used_MB, b.free_mb,b.path, b.header_status
from v$asm_disk b, v$asm_diskgroup a 
where a.group_number (+) =b.group_number 
order by b.group_number,b.name;
DISKGROUP  DISKNAME       TOTAL_MB    USED_MB    FREE_MB PATH                           HEADER_STATU
---------- ------------ ---------- ---------- ---------- ------------------------------ ------------
CRS        CRS_0000           7177        396       6781 /dev/oracleasm/disks/CRS1      MEMBER
DATA       DATA_0000          7177        838       6339 /dev/oracleasm/disks/DATA1     MEMBER
DATA       DATA_0001         10236       1183       9053 /dev/oracleasm/disks/DATA2     MEMBER
FRA        FRA_0000           6118        366       5752 /dev/oracleasm/disks/FRA1      MEMBER

Observe that both DATA1 and DATA2 are now part of DATA diskgroup. Now we can remove the old disk DATA1 from the diskgroup

SQL> alter diskgroup DATA drop disk 'DATA_0000'; 
Diskgroup altered.

Wait for re-balance operation to complete

SQL> Select operation, state, est_work, est_minutes from v$asm_operation; 
OPERA STAT   EST_WORK EST_MINUTES
----- ---- ---------- -----------
REBAL RUN         836           0

Once re-balance operation is completed, check the disk details via below query and you must see DATA1 disk marked as FORMER

set lines 999;
col diskgroup for a10
col diskname for a12
col path for a30
select a.name DiskGroup,b.name DiskName, b.total_mb, (b.total_mb-b.free_mb) Used_MB, b.free_mb,b.path, b.header_status
from v$asm_disk b, v$asm_diskgroup a 
where a.group_number (+) =b.group_number 
order by b.group_number,b.name;
DISKGROUP  DISKNAME       TOTAL_MB    USED_MB    FREE_MB PATH                           HEADER_STATU
---------- ------------ ---------- ---------- ---------- ------------------------------ ------------
                                 0          0          0 /dev/oracleasm/disks/DATA1     FORMER
CRS        CRS_0000           7177        396       6781 /dev/oracleasm/disks/CRS1      MEMBER
DATA       DATA_0001         10236       1183       9053 /dev/oracleasm/disks/DATA2     MEMBER
FRA        FRA_0000           6118        366       5752 /dev/oracleasm/disks/FRA1      MEMBER

You can later choose to complete drop DATA1 disk via below command and ask storage team to reclaim the mount points

[root@oraracn1 ~]# oracleasm deletedisk DATA1 /dev/sdb1

You can also achieve above via ASMCA but make sure you monitor re-balancing manually.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
DBA Genesis by Arun Kumar - 7M ago

This article demonstrates MySQL installation on Oracle Enterprise Linux 6.5 version.

Installation of MySQL

Make sure you are able to connect internet via virtual machine. Try to ping google.com and proceed with installation of MySQL 5.6

# yum install mysql mysql-server -y

Start mysql service and enable it to auto-start on reboot

# chkconfig mysqld on
# service mysqld start

Initiate mysql secure installation to remove test databases and anonymous users created by default. Just hit enter when prompted for root password and then change it

mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none): 
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
Set root password? [Y/n] Y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] Y
 ... Success!
Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] Y
 ... Success!
By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] Y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] Y
 ... Success!
Cleaning up...
All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!
Crate MySQL Database

Once MySQL installation is done, let us connect to the database

$ mysql --user=root --password
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.1.67 Source distribution
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

Use below command to create new database

mysql> create database mydb;
Query OK, 1 row affected (0.01 sec)
mysql>

List all the databases

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)
mysql>

To switch between databases use below command

mysql> use mydb;
Database changed
mysql>

You can make new connections directly to the database as follows.

$ mysql --user=root --database=mydb --password
Enter password:

Installation Done!









Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Before you can increase memory in oracle database, you must first understand four oracle memory parameters which govern the instance:

  • SGA_TARGET and SGA_MAX_SIZE
  • MEMORY_TARGET and MEMORY_MAX_TARGET


SGA_TARGET and SGA_MAX_SIZE

Important: If you set MEMORY_TARGET, they oracle will perform AMM (automatic memory management) and both SGA + PGA are managed within the allocated memory. No need to touch SGA_TARGET / SGA_MAX_SIZE parameters.

Let us assume we have 10 GB physical RAM on a server. Which mean, at any given point we can max use 10 GB of RAM but not more than that. Also it does not mean that every-time we are using entire 10 GB of RAM. The same way SGA_TARGET defines the total size of SGA. SGA_MAX_SIZE define the total max value SGA_TARGET can take.

For example, server RAM is 10 GB, SGA_MAX_SIZE is 5 GB and SGA_TARGET is 3 GB. This means that I can max increase SGA_TARGET to 5 GB and not beyond that.

SGA_MAX_SIZE is not dynamic parameter but SGA_TARGET is.


MEMORY_TARGET and MEMORY_MAX_TARGET

From 11g onward, you do not need to manage SGA and PGA separately. You can allocate MEMORY_TARGET parameter and oracle will handle both SGA + PGA. You do not even need to set SGA_TARGET or SGA_MAX_SIZE.

Let us assume our previous example, we have 10 GB RAM on server. And we want to allocate 5 GB to Oracle. This can be simple done by setting MEMORY_TARGET to 5 GB. MEMORY_MAX_TARGET defines the maximum value MEMORY_TARGET can go.

Example, server RAM is 10 GB, MEMORY_MAX_TARGET is 7 GB and MEMORY_TARGET is 5 GB. This means I can max increase MEMORY_TARGET to 7 GB and not beyond that.

MEMORY_MAX_TARGET is not dynamic parameter but MEMORY_TARGET is

If MEMORY_TARGET is set to 5 GB, oracle will manage PGA + SGA withing 5 GB.


IF YOU SET MEMORY_TARGET

  • SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET are set to 0, 60% of memory mentioned in MEMORY_TARGET is allocated to SGA and rest 40% is kept for PGA
  • SGA_TARGET and PGA_AGGREGATE_TARGET are set to non-zero values, these values will be considered minimum values.
  • SGA_TARGET is set to non zero value and PGA_AGGREGATE_TARGET is not set. Still these values will be autotuned and PGA_AGGREGATE_TARGET will be initialized with value of (MEMORY_TARGET-SGA_TARGET).
  • PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set. Still both parameters will be autotunes. SGA_TARGET will be initialized to a value of (MEMORY_TARGET-PGA_AGGREGATE_TARGET).


HOW TO INCREASE MEMORY_TARGET IN ORACLE

SQL> show parameter target;
SQL> ALTER SYSTEM SET MEMORY_MAX_TARGET=3G SCOPE=SPFILE;
shut immediate;

Also make sure that /dev/shm is more than 3G otherwise you will get error at instance startup

df -h /dev/shm                  -> in my case, it was 2 GB. Increasing to 5 GB
mount -t tmpfs shmfs -o size=5G /dev/shm
df -h /dev/shm

Make /dev/shm value permanent in /etc/fstab file

vi /etc/fstab
change
tmpfs                   /dev/shm                tmpfs   defaults        0 0
to
tmpfs                   /dev/shm                tmpfs   size=5G        0 0

Start the database instance and increase MEMORY_TARGET

startup;
show parameter target;
ALTER SYSTEM SET MEMORY_TARGET=2560m;


In this activity, we have increased memory in oracle database by adding size to MEMORY_TARGET parameter.


Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Each and every Golden Gate process takes approx 25 - 50 MB of memory or sometimes more depending upon the transactions.

Here is one script which will help you find the total memory usage on source and target server. This script is written for linux

#!/bin/bash
###############################
# determine the OS type
###############################
OSNAME=`uname`
case "$OSNAME" in
  "SunOS")
    echo "OSNAME = $OSNAME"
    ;;
  "Linux")
    echo "OSNAME = $OSNAME"
    ;;
  "*")
    echo "This script has not been verified on $OSNAME"
    exit 1
    ;;
esac
###############################
# set the temp file
###############################
TMPFILE=/tmp/pmem.tmp
if [ -f $TMPFILE ]
then
  rm -f $TMPFILE
fi
################################
# loop over the gg process types
################################
PROCESSES="extract replicat"
for PROCESS in $PROCESSES
do
  FLAG=""
  FLAG=`ps -ef | grep $PROCESS`
  if [ -z "FLAG" ]
  then
    echo "No $PROCESS processes found"
  else
    echo
    echo "#####################################"
    echo "# Individual $PROCESS Process Usage #"
    echo "#####################################"
    case "$OSNAME" in
      "Linux")
        ps -C $PROCESS -O rss > $TMPFILE
        cat $TMPFILE | grep $PROCESS | awk '{print $2/1024, "MB", $12}' | sort -k 2
        ;;
      "SunOS")
        ps -efo vsz,uid,pid,ppid,pcpu,args | grep -v grep | grep $PROCESS > $TMPFILE
        cat $TMPFILE | grep $PROCESS | awk '{print $1/1024, "MB", $8}' | sort -k 2
        ;;
      "*")
        echo "This script has not been verified on $OSNAME"
        exit 1
        ;;
    esac
    rm -f $TMPFILE
    echo
    echo "#####################################"
    echo "#   Total $PROCESS Process Usage    #"
    echo "#####################################"
    case "$OSNAME" in
      "Linux")
        ps -C $PROCESS -O rss > $TMPFILE
        cat $TMPFILE | grep $PROCESS | awk '{count ++; sum=sum+$2; } END \
          { print "Number of processes      =",count; \
          print "AVG Memory usage/process =",sum/1024/count, "MB"; \
          print "Total memory usage       =", sum/1024,  " MB"}'
        ;;
      "SunOS")
        ps -efo vsz,uid,pid,ppid,pcpu,comm | grep -v grep | grep $PROCESS > $TMPFILE
        cat $TMPFILE | awk '{count ++; sum=sum+$1; } END \
          { print "Number of processes      =",count; \
          print "AVG Memory usage/process =",sum/1024/count, "MB"; \
          print "Total memory usage       =", sum/1024,  " MB"}'
        ;;
      "*")
        echo "This script has not been verified on $OSNAME"
        exit 1
        ;;
    esac
    rm -f $TMPFILE
  fi
done
exit 0

Below is the sample output on golden gate source

[oracle@ggprod gg]$ ./gg_memory_usage.sh
OSNAME = Linux
#####################################
# Individual extract Process Usage #
#####################################
34.5703 MB DP1
49.1172 MB EXT1
#####################################
#   Total extract Process Usage    #
#####################################
Number of processes      = 2
AVG Memory usage/process = 41.8438 MB
Total memory usage       = 83.6875  MB
#####################################
# Individual replicat Process Usage #
#####################################
#####################################
#   Total replicat Process Usage    #
#####################################
Number of processes      =
awk: cmd. line:2: fatal: division by zero attempted

Ignore the error at the end as there are not replicate details available on source server.

The same script can be run on target server and it will give you the details of replicat

[oracle@ggdev gg]$ ./gg_memory_usage.sh
OSNAME = Linux
#####################################
# Individual extract Process Usage #
#####################################
#####################################
#   Total extract Process Usage    #
#####################################
Number of processes      =
awk: cmd. line:2: fatal: division by zero attempted
#####################################
# Individual replicat Process Usage #
#####################################
34.3008 MB REP1
#####################################
#   Total replicat Process Usage    #
#####################################
Number of processes      = 1
AVG Memory usage/process = 34.3008 MB
Total memory usage       = 34.3008  MB
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Database performance is a major concern for a DBA. SQLs are the ones which needs proper DB management in order to execute well. At times the application team might tell you that the database is running slow. You can run below query to get the top 5 resource intensive SQL with SQL ID and then give it to application team to optimize them.

Query to find top 5 resource intensive SQL
col Rank for a4
SELECT *
FROM (SELECT RANK () OVER 
(PARTITION BY "Snap Day" ORDER BY "Buffer Gets" + "Disk Reads" DESC) AS "Rank", i1.*
FROM (SELECT TO_CHAR (hs.begin_interval_time, 'MM/DD/YY' ) "Snap Day",
SUM (shs.executions_delta) "Execs",
SUM (shs.buffer_gets_delta) "Buffer Gets",
SUM (shs.disk_reads_delta) "Disk Reads",
ROUND ( (SUM (shs.buffer_gets_delta)) / SUM (shs.executions_delta), 1 ) "Gets/Exec",
ROUND ( (SUM (shs.cpu_time_delta) / 1000000) / SUM (shs.executions_delta), 1 ) "CPU/Exec(S)",
ROUND ( (SUM (shs.iowait_delta) / 1000000) / SUM (shs.executions_delta), 1 ) "IO/Exec(S)",
shs.sql_id "Sql id",
REPLACE (CAST (DBMS_LOB.SUBSTR (sht.sql_text, 50) AS VARCHAR (50) ), CHR (10), '' ) "Sql"
FROM dba_hist_sqlstat shs INNER JOIN dba_hist_sqltext sht
ON (sht.sql_id = shs.sql_id)
INNER JOIN dba_hist_snapshot hs
ON (shs.snap_id = hs.snap_id)
HAVING SUM (shs.executions_delta) > 0
GROUP BY shs.sql_id, TO_CHAR (hs.begin_interval_time, 'MM/DD/YY'), 
CAST (DBMS_LOB.SUBSTR (sht.sql_text, 50) AS VARCHAR (50) )
ORDER BY "Snap Day" DESC) i1
ORDER BY "Snap Day" DESC)
WHERE "Rank" <= 5 AND "Snap Day" = TO_CHAR (SYSDATE, 'MM/DD/YY');
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

When you issue archive backup commands via RAMN, it will backup all the archive logs. Sometimes, you might need to backup only a particular archive log sequence. Below command will help you backup archive logs between specific sequence

RMAN> BACKUP ARCHIVELOG FROM SEQUENCE 288 UNTIL SEQUENCE 388 DELETE INPUT;

The above command will backup archive logs from 288 to 388 sequence number.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

As a DBA, you must keep an eye on the largest tables in the database. There are many things that get impacted with the largest objects like DB performance, growth, index rebuild etc. The below query gives you the top 10 largest tables in oracle database.

Query to check top 10 largest tables in Oracle
SELECT * FROM
(select 
 SEGMENT_NAME, 
 SEGMENT_TYPE, 
 BYTES/1024/1024/1024 GB, 
 TABLESPACE_NAME 
from 
 dba_segments
order by 3 desc ) WHERE
ROWNUM <= 10
Read Full Article
Visit website

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview