Hi All,

I am the CEO here at Kudrant Academy and have over 10 years oracle DBA experience. I’m thrilled to be your guide throughout this course.

In this course, I will teach you how to install, create and configure oracle software and database on a Linux Oracle server. You will also learn how to perform database administration tasks such as starting and shutting down an oracle database, monitor space on the server and database, create and manage database objects and user accounts, configure oracle networking files, load and unload data from a database and other tasks.

You will also learn how to write SQL scripts to perform automated tasks such as auditing, monitoring space, take database export backup, compress and delete old archive redo logs, rotate listener logs.

Throughout the course you will develop skills for  database administration and creating dynamic SQL.

By the end of this course, you’ll be ready for the RMAN Backup and Recovery course and to start your journey towards a challenging and rewarding career as an oracle database administrator.

It will be up to you to “take action” by completing all the course assignments and lab exercises.

Ready?

Let’s get started!

Imgard Ekokobe
CEO/Database Engineer
Kudrant Academy

Next

After completing this course, you will be able to:

• Install, create and administer Oracle Database 11gR2 and 12C2 databases.
• Configure an Oracle database for an application.
• Create, manage and monitor storage structures.
• Configure networking files.
• Monitor the database.
• Move data between databases and files.

Next

Course Environment

The system runs on centos 7 with Intel Core I3-4130 3.40 CPU and 8GB of system memory. The system will be running RAID 1.

There will be seven directories (/u01 through /u07) to store  your oracle files with a total storage size of 500G. The /home/oracle directory will hold Oracle program files or binaries. All other directories named /u02, /u03, through /u07 will hold database files. This configuration will easily accommodate a seven-disk database layout.

Each group of students will be assigned an Oracle instance in which to work. This document assumes 2 database instances and 2 students per group, but the exact numbers can be determined later. The instances will be named kud01, kud02, kud03,  … kud10. Each of the disk sets /u02 through /u07 will contain a subdirectory for each instance where the actual database files will be stored. Thus, you will create these subdirectories:

/u02/kud01

/u02/kud02

/u02/kud03

/u02/kud04

…

/u02/kud10

/u03/kud01

/u03/kud02

/u03/kud03

/u03/kud04

…

/u03/kud10

etc…

You will access these directories via links in their own directories.

Each student will receive a LINUX account for class work. The naming convention for the usernames will be the name of the instance the student will use, plus a letter of the alphabet. That is, the students in the first group will work on database instance kud01 and be assigned the usernames kud01a, kud01b, and kud01c. The next group will be kud02a, kud02b, and kud02c, and so on.

Here is an example of the structure of a student account. The arrow indicates that the directory or file is actually a link, and shows to what target the link refers.

kud01a/

.oraenv

.profile

.tcshrc

/u01

/u02/kud01

control01.con

system01.dbf

sysauxs01.dbff

user_indx01.dbf

/u03/kud01

control02.con

undotbs01.dbf

/u04/kud01

control03.con

user_data01.dbf

/u05/kud01

user_data02.dbf

indx01.kudf

temp01.kudf

temp_user01.dbf

/u06/kud01

adump/

cdump/

redo0101.log

redo0201.log

redo0301.log

udump/

/u07/kud01

arch/

redo0102.log

redo0202.log

redo0302.log

initkud01.ora  ->  /home/oracle/app/oracle/product/12.2.0/dbhome_1
/dbs/initkud01.ora

scripts/

Here are some notes on the account contents:

• The .oraenv file is a shell script that will set Oracle-related environment variables (e.g., ORACLE_HOME and ORACLE_SID) and display those values. The .profile  (depending on the shell chosen by the student) will run .oraenv at login time.
• The adump, cdump, and trace directories will hold trace files, and correspond to the background_dump_dest, core_dump_dest, parameters in the parameter file, respectively.
• The arch directory is for archiving redo logs, and corresponds to the log_archive_dest parameter.
• The parameter file (initkud##.ora) requires a number of customizations in order to handle this directory structure, as well as for running of multiple instances on this machine (e.g., appropriate sizing of the system global area, or SGA).
• The scripts directory will hold the database creation scripts, as well as any scripts provided by your coaches.
• Be aware that the /u01 through /u07 directories and the initkud##.ora file are shared. Therefore, everyone should take appropriate precautions to not overwrite changes made by other members of the group. Students are free to create subdirectories within their own accounts for class work but should not modify or create directories in the /u01 through /u07 directories.

The Basics You Need

You should already know the basics of relational database technology. Specifically, you should know how to construct and ERD, normalize the design, create objects, insert and load data, and retrieve data.

Requirements for the Course

Everyone in the class will participate as a team member.

The class will be divided into teams. Each member of the team will have DBA privileges within one instance. Team responsibilities include:

• Install Oracle instance. The instances will be named kud01, kud02, kud03, kud04, … kud10. Each team will install one of the instances.
• Set up accounts. After the installation, the KUDBAs will set up their KUDBA accounts. When the accounts have been setup, KUDBAs will turn over sys and system accounts to your coaches. The KUDBA accounts should be setup with a script, not interactively.
• Design and create objects. Each instance should have ten tables that are owned by the KUDBA team. Create an ERD with the 10 tables. One table should be sized for 50,000 rows. The other nine tables should be sized for 1,000 rows each. Each team member is responsible for sizing and creating some tables.
• Use scripts to create all objects. Create tablespaces on disks.
• Create sized tables in the tablespaces. Place objects on the specific disks. The largest table should be partitioned into two portions and stored on two different disks.
• Populate tables with test data. Create and load test data. All team members are responsible for loading data into their tables. Remember to use compute statistics after the loads.
• Backup the database. Use the export RMAN and flashback commands to backup the fully-loaded database.
• Create user accounts for end users. Create user accounts for a different team for your database. Those users should be able to select, insert, delete, and update all of your tables. They should not, however, have KUDBA privileges to objects in your instance.

Everyone in the class will participate as an individual.

Individual responsibilities include:

• Perform these maintenance tasks:

• Collect statistics on how your instance is running. Find scripts on the internet to help you.
• Manually add and delete columns from one table. Do not use the drop column or add column commands.
• Monitor space utilization.
• Monitor the alert log.
• Test the recovery capability. To do so, with a test table, export and import an object or data.
• Startup and shutdown the instance after some changes.

• Design and run a benchmark test. Design a workload that will stress your database. Set up scripts or code that can run continuously.
• Configure Oracle and tune the configuration.

Each KUDBA will be an end user.

A KUDBA as an end user permissions and abilities include:

• As an end user, request catalog information that will allow you to reverse engineer an ERD of their database. Draw an ERD of their database.
• Design a workload that will run against the other team’s database. Do not tell them what the workload will be. At intervals unknown to the KUDBA team, kick off the workload.

Next

Description

SSH, Telnet and Rlogin are three ways you can log on to multi-user computer system such as Linux/unix/VMS over a network.Linux servers usually present a command-line interface to the user, much like the ‘Command Prompt’ or ‘MS-DOS Prompt’ in Windows. The system prints a prompt, and you type commands which the system will execute.Using this type of interface, there is no need for you to be sitting at the same machine you are typing commands to. The commands, and responses, can be sent over a network, so you can sit at one computer and give commands to another one, or even to more than one.SSH, Telnet and Rlogin are network protocols that allow you to do this. On the computer you sit at, you run a client, which makes a network connection to the other computer (the server). The network connection carries your keystrokes and commands from the client to the server, and carries the server’s responses back to you.

This hands-on lab will introduce you to Putty, a Telnet and SSH terminal software for Unix and Windows platforms that allows you to remotely access your oracle database and server — right from your computer over the network!

PuTTY is a free and open source and network file transfer Application which supports several network protocol.

You will install this tool on your computer and you must have internet connection.

We'll take a look at how to install and set up Putty and then connection to your database server and database.

When we're finished, you'll have the knowledge necessary to connect and run commands on your database from the Putty,

Some features of PuTTY

– PuTTY supports multiple variations on the secure remote terminal.

– It provides control over the SSH encryption key and protocol version.

– It stores hosts and preferences for later usage.`

– It allows remote, local or dynamic port forwarding with SSH.

Key Objectives

• Download Putty.
• Install and Configure Putty on your machine.
• Connect to an oracle Linux database remotely.
• Run sql commands on a  linux oracle database.

Next

Description

The client enables services and their applications to reside on different computers and communicate as peer applications. The main function is to establish network sessions and transfer data between a client machine and a server or between two servers.Essentially, the client (SQL*NET) provides the software layer between Oracle and the networking software, providing seamless communication between an Oracle client machine (running, for example, SQL*Plus) and the database server or from one database server to another

This hands-on lab will introduce you to the Oracle Client, a software layer between Oracle and the networking software, providing seamless communication between an Oracle client machine (running, for example, SQL*Plus) and the database server or from one database server to another. It allows you to remotely or locally  access your oracle database — right from your computer over the network!

You will install this tool on your computer and you must have internet connection.

We'll take a look at how to install and set up the oracle client and then connection to your oracle database.

When we're finished, you'll have the knowledge necessary to connect and run commands on your database from the SQL*Plus,

Key Objectives

• Download oracle client.
• Install and Configure client on your windows machine.
• Connect to an oracle Linux database locally and remotely from SQL*Plus.
• Run sql commands on a linux oracle database.

Next

Description

The client enables services and their applications to reside on different computers and communicate as peer applications. The main function is to establish network sessions and transfer data between a client machine and a server or between two servers.Essentially, the client (SQL*NET) provides the software layer between Oracle and the networking software, providing seamless communication between an Oracle client machine (running, for example, SQL*Plus) and the database server or from one database server to anotherThis hands-on lab will introduce you to the Oracle Client, a software layer between Oracle and the networking software, providing seamless communication between an Oracle client machine (running, for example, SQL*Plus) and the database server or from one database server to another. It allows you to remotely or locally  access your oracle database — right from your computer over the network!You will install this tool on your computer and you must have internet connection.We'll take a look at how to install and set up the oracle client and then connection to your oracle database.When we're finished, you'll have the knowledge necessary to connect and run commands on your database from the SQL*Plus,Key ObjectivesDownload oracle client.Install and Configure client on your windows machine.Connect to an oracle Linux database locally and remotely from SQL*Plus.Run sql commands on a linux oracle database.

Next

Next

Next

• Describe your role as a database administrator (DBA) and explain typical tasks and tools.
• Plan an Oracle software installation.
• Install the Oracle database software.
• Configure the Oracle database software installation.

Next

You have configured the Linux  server for the installation of the oracle software.

Now, you are ready to install oracle.

Follow the link to download the steps to install the oracle software on Linux.

Steps To Install Oracle On Linux

Next

As you may have already noticed, the installation of oracle on Linux is more complicated that on windows. It requires  a lot of planning and some requirements have to be configured before the installations occurs. For example, you will have to configure the memory, hardware, storage, system and OS requirements.

It is your job to download and read the entire installation documentation for the oracle release you are planning to install. You will gather the pre-install requirements, document them and forward these to the sysadmins who will then configure these changes on the Linux server if it is a brand new server that is built out.

After the sysadmins configure the server and turn it over to you, it is your job to verify that all the prerequisites have been met on the server before you start the installation.

Follow the link to download and review the oracle installation guide for the oracle database 11gR2 version in its entirety and then create a guide documenting the pre-installation steps.

Submit your document to the class page.

oracle database 11gR2 quick installation guide

1. Login into the server as root.
   sudo su – root
2. Check the RAM size.
    [root@69-64-69-66 ~]# grep MemTotal /proc/meminfo
   MemTotal: 7969912 kB

   Minimum: 1 GB of RAM
   Recommended: 2 GB of RAM or more

3. Check the size of the swap space.
    [root@69-64-69-66 ~]# grep SwapTotal /proc/meminfo
   SwapTotal: 8388604 kB

   Available RAM	Swap Space Required
   Between 1 GB and 2 GB	1.5 times the size of the RAM
   Between 2 GB and 16 GB	Equal to the size of the RAM
   More than 16 GB	16 GB

4. Check the available RAM and swap space.
   [root@69-64-69-66 ~]# free
   total used free shared buff/cache available
   Mem: 7969912 135732 7490052 57916 344128 7523004
   Swap: 8388604 0 8388604

   Oracle recommends that you take multiple values for the available RAM and swap space before finalizing a value. This is because the available RAM and swap space keep changing depending on the user interactions with the computer.

5. Check that the system architecture can run the software.
   [root@69-64-69-66 ~]# uname -m
   x86_64
6. Check the amount of shared memory.
   [root@69-64-69-66 ~]# df -h /dev/shm/
   Filesystem Size Used Avail Use% Mounted on
   tmpfs 3.9G 0 3.9G 0% /dev/shm

   Note: In Oracle Database 11g, the Automatic Memory Management feature requires more shared memory (/dev/shm)and file descriptors. The shared memory should be sized to be at least the greater of MEMORY_MAX_TARGETand MEMORY_TARGET for each Oracle instance on that computer.

7. Check that the system architecture can run the oracle software.
   [root@69-64-69-66 ~]# uname -m
   x86_64

   Note: This command displays the processor type. Verify that the processor architecture matches the Oracle software release to install. If you do not see the expected output, then you cannot install the software on this system.

8. Check the amount of disk space available in the /tmp directory.
   [root@69-64-69-66 ~]# df -h /tmp
   Filesystem Size Used Avail Use% Mounted on
   /dev/mapper/VolGroup00-tmp 20G 45M 19G 1% /tmp

   At least 1 GB of disk space in the /tmp directory recommended.

9. Check the amount of free disk space on the system.
   [root@69-64-69-66 ~]# df -h /tmp
   Filesystem Size Used Avail Use% Mounted on
   /dev/mapper/VolGroup00-tmp 20G 45M 19G 1% /tmp
   [root@69-64-69-66 ~]# df -h
   Filesystem Size Used Avail Use% Mounted on
   /dev/mapper/VolGroup00-vol3 385G 1.1G 364G 1% /
   devtmpfs 3.8G 0 3.8G 0% /dev
   tmpfs 3.9G 0 3.9G 0% /dev/shm
   tmpfs 3.9G 57M 3.8G 2% /run
   tmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup
   /dev/sda1 20G 159M 18G 1% /boot
   /dev/mapper/VolGroup00-u01 178G 61M 168G 1% /u01
   /dev/mapper/VolGroup00-u02 50G 53M 47G 1% /u02
   /dev/mapper/VolGroup00-u06 77G 53M 73G 1% /u06
   /dev/mapper/VolGroup00-u04 39G 49M 37G 1% /u04
   /dev/mapper/VolGroup00-u05 77G 53M 73G 1% /u05
   /dev/mapper/VolGroup00-var 29G 222M 28G 1% /var
   /dev/mapper/VolGroup00-u03 39G 49M 37G 1% /u03
   /dev/mapper/VolGroup00-u07 99G 61M 94G 1% /u07
   /dev/mapper/VolGroup00-tmp 20G 45M 19G 1% /tmp
   /dev/mapper/VolGroup00-opt 29G 45M 28G 1% /opt
   /dev/mapper/VolGroup00-u08 99G 61M 94G 1% /u08
   /dev/sdc1 30G 45M 28G 1% /mnt/blockstorage
   tmpfs 779M 0 779M 0% /run/user/1005
   tmpfs 779M 0 779M 0% /run/user/0

   The following tables describe the disk space requirements for software files, and data files for each installation type on Linux x86-64:

   Installation Type	Requirement for Software Files (GB)
   Enterprise Edition	4.7
   Standard Edition	4.6

   Installation Type	Requirement for Data Files (GB)
   Enterprise Edition	1.7
   Standard Edition	1.5

10. Check the distribution and version of Linux installed.
    [root@69-64-69-66 ~]# cat /proc/version
    Linux version 3.10.0-514.10.2.el7.x86_64 (builder@kbuilder.dev.centos.org) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-11) (GCC) ) #1 SMP Fri Mar 3 00:04:05 UTC 2017

    Centos 7 uses thesame source code as Red Hat Linux 7 (RHEL7). Compare the kernel version to that of the RHEL 7 to be sure they match.

    On Red Hat Enterprise Linux 7 – 3.10.0-54.0.1.el7.x86_64 or later
11. Check whether the required kernel is installed.
    [root@69-64-69-66 ~]# uname -r
    3.10.0-514.10.2.el7.x86_64

    The output shows the kernel version (3.10.0) and errata level (514.10.2.el7) on the system.

12. Check if the required packages are installed.
    [root@69-64-69-66 yum]# rpm -q –qf ‘%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n’ binutils.x86_64 compat-libcap1.x86_64 compat-libstdc++-33.i686 compat-libstdc++-33.x86_64 gcc.x86_64 gcc-c++.x86_64 glibc.i686 glibc.x86_64 glibc-devel.i686 glibc-devel.x86_64 ksh libaio.i686 libaio.x86_64 libaio-devel.i686 libaio-devel.x86_64 libgcc.i686 libgcc.x86_64 libstdc++.i686 libstdc++.x86_64 libstdc++-devel.i686 libstdc++-devel.x86_64 libXi.i686 libXi.x86_64 libXtst.i686 libXtst.x86_64 make.x86_64 sysstat.x86_64
    binutils-2.25.1-22.base.el7(x86_64)
    package compat-libcap1.x86_64 is not installed
    package compat-libstdc++-33.i686 is not installed
    package compat-libstdc++-33.x86_64 is not installed
    package gcc.x86_64 is not installed
    package gcc-c++.x86_64 is not installed
    package glibc.i686 is not installed
    glibc-2.17-157.el7_3.1(x86_64)
    package glibc-devel.i686 is not installed
    package glibc-devel.x86_64 is not installed
    ksh-20120801-26.el7(x86_64)
    package libaio.i686 is not installed
    libaio-0.3.109-13.el7(x86_64)
    package libaio-devel.i686 is not installed
    package libaio-devel.x86_64 is not installed
    package libgcc.i686 is not installed
    libgcc-4.8.5-11.el7(x86_64)
    package libstdc++.i686 is not installed
    libstdc++-4.8.5-11.el7(x86_64)
    package libstdc++-devel.i686 is not installed
    package libstdc++-devel.x86_64 is not installed
    package libXi.i686 is not installed
    libXi-1.7.4-2.el7(x86_64)
    package libXtst.i686 is not installed
    libXtst-1.2.2-2.1.el7(x86_64)
    make-3.82-23.el7(x86_64)
    sysstat-10.1.5-11.el7(x86_64)
13. Install all missing packages.
    yum install binutils.x86_64
    yum install compat-libcap1.x86_64
    yum install compat-libstdc++-33.i686
    yum install compat-libstdc++-33.x86_64
    yum install gcc.x86_64
    yum install gcc-c++.x86_64
    yum install glibc.i686
    yum install glibc.x86_64
    yum install glibc-devel.i686
    yum install glibc-devel.x86_64
    yum install ksh
    yum install libaio.i686
    yum install libaio.x86_64
    yum install libaio-devel.i686
    yum install libaio-devel.x86_64
    yum install libgcc.i686
    yum install libgcc.x86_64
    yum install libstdc++.i686
    yum install libstdc++.x86_64
    yum install libstdc++-devel.i686
    yum install libstdc++-devel.x86_64
    yum install libXi.i686
    yum install libXi.x86_64
    yum install libXtst.i686
    yum install libXtst.x86_64
    yum install make.x86_64
    yum install sysstat.x86_64
14. Check if the oinstall group exsist
    [root@69-64-69-66 ~]# more /etc/oraInst.loc
    /etc/oraInst.loc: No such file or directory

    If the output of this command shows the oinstall group name, then the group exists.If the oraInst.loc file exists, then the output from this command is similar to the following:

    inventory_loc=/u01/app/oraInventory
    inst_group=oinstall

15. Create the create the oinstall and dba groups.

    # /usr/sbin/groupadd oinstall
    # /usr/sbin/groupadd dba

16. Check if the oracle user exists and belongs to the correct groups.
    [root@69-64-69-66 ~]# id oracle
    uid=1004(oracle) gid=500(oinstall) groups=500(oinstall),505(dba)

    If the oracle user exists, then this command displays information about the groups to which the user belongs. The output should be similar to the following, indicating that oinstall is the primary group and dbais a secondary group.
    If the oracle user exists, but its primary group is not oinstall or it is not a member of the dbagroup, then enter the following command:

    # /usr/sbin/usermod -g oinstall -G dba oracle
17. Create the oracle account if it does not exist.
    # /usr/sbin/useradd -g oinstall -G dba oracle

    This command creates the oracle user and specifies oinstall as the primary group and dba as the secondary group.

18. Set the passwd for the oracle user
    # passwd oracle
19. Check the current values of the kernel parameters.

    semmsl, semmns, semopm, and semmni

    [root@69-64-69-66 ~]# /sbin/sysctl -a | grep sem
    kernel.sem = 250 32000 32 128
    kernel.sem_next_id = -1

    shmall, shmmax, and shmmni

    [root@69-64-69-66 ~]# /sbin/sysctl -a | grep shm
    kernel.shm_next_id = -1
    kernel.shm_rmid_forced = 0
    kernel.shmall = 18446744073692774399
    kernel.shmmax = 18446744073692774399
    kernel.shmmni = 4096
    vm.hugetlb_shm_group = 0

    file-max

    [root@69-64-69-66 ~]# /sbin/sysctl -a | grep file-max
    fs.file-max = 787308

    ip_local_port_range

    [root@69-64-69-66 ~]# /sbin/sysctl -a | grep ip_local_port_range
    net.ipv4.ip_local_port_range = 32768 60999

    rmem_default

    [root@69-64-69-66 ~]# /sbin/sysctl -a | grep rmem_default
    net.core.rmem_default = 212992

    rmem_max

    [root@69-64-69-66 ~]# /sbin/sysctl -a | grep rmem_max
    net.core.rmem_max = 212992

    wmem_default

    [root@69-64-69-66 ~]# /sbin/sysctl -a | grep wmem_default
    net.core.wmem_default = 212992

    wmem_max

    [root@69-64-69-66 ~]# /sbin/sysctl -a | grep wmem_max
    net.core.wmem_max = 212992

    Kernel parameters and shell limit values chart:

    Parameter	Minimum Value	File
    semmslsemmnssemopm semmni	25032000100 128	/proc/sys/kernel/sem
    shmall	2097152	/proc/sys/kernel/shmall
    shmmax	Minimum: 536870912Maximum: A value that is 1 byte less than the physical memoryRecommended: More than half the physical memory See My Oracle Support Note 567506.1 for additional information about configuring shmmax.	/proc/sys/kernel/shmmax
    shmmni	4096	/proc/sys/kernel/shmmni
    file–max	6815744	/proc/sys/fs/file-max
    ip_local_port_range	Minimum: 9000Maximum: 65500	/proc/sys/net/ipv4/ip_local_port_range
    rmem_default	262144	/proc/sys/net/core/rmem_default
    rmem_max	4194304	/proc/sys/net/core/rmem_max
    wmem_default	262144	/proc/sys/net/core/wmem_default
    wmem_max	1048576	/proc/sys/net/core/wmem_max
    aio-max-nr	1048576Note: This value limits concurrent outstanding requests and should be set to avoid I/O subsystem failures.	/proc/sys/fs/aio-max-nr

20. Eedit the /etc/sysctl.conf file, if  any kernel parameter is different from the recommended value.

    fs.aio-max-nr = 1048576
    fs.file-max = 6815744
    kernel.shmall = 2097152
    kernel.shmmax = 536870912
    kernel.shmmni = 4096
    kernel.sem = 250 32000 100 128
    net.ipv4.ip_local_port_range = 9000 65500
    net.core.rmem_default = 262144
    net.core.rmem_max = 4194304
    net.core.wmem_default = 262144
    net.core.wmem_max = 1048576

21. Make the current values of the kernel parameters available in the active kernel memory.

    # /sbin/sysctl -p

    Review the output from this command to verify that the values are correct. If the values are incorrect, edit the /etc/sysctl.conf file, then enter this command again.

22. Confirm that the values are set correctly.
    #/sbin/sysctl -a
23. Check Resource Limits for the Oracle  User.Check the soft and hard limits for the file descriptor setting.
    [oracle@69-64-69-66 ~]$ id
    uid=1004(oracle) gid=500(oinstall) groups=500(oinstall),505(dba)
    [oracle@69-64-69-66 ~]$ ulimit -Sn
    1024
    [oracle@69-64-69-66 ~]$ ulimit -Hn
    4096
    [oracle@69-64-69-66 ~]$

    Check the soft and hard limits for the number of processes available to a user.

    [oracle@69-64-69-66 ~]$ ulimit -Su
    4096
    [oracle@69-64-69-66 ~]$ ulimit -Hu
    31044

    Check the soft limit for the stack setting

    [oracle@69-64-69-66 ~]$ ulimit -Ss
    8192
    [oracle@69-64-69-66 ~]$ ulimit -Hs
    unlimited

    Resource Shell Limit	Resource	Soft Limit	Hard Limit
    Open file descriptors	nofile	at least 1024	at least 65536
    Number of processes available to a single user	nproc	at least 2047	at least 16384
    Size of the stack segment of the process	stack	at least 10240 KB	at least 10240 KB, and at most 32768 KB

    If necessary, update the resource limits in the /etc/security/limits.conf configuration file for the oracle user.

24. Check the filesystems and space on mounted on the server.
    df -k
25. Create the oracle directories with the right permissions.The Oracle base directory must have 3 GB of free disk space, or 4 GB of free disk space if you choose not to create a separate Oracle data file directory.

    # mkdir -p /u01/app/

    # chown -R oracle:oinstall /u01/app/

    # chmod -R 775 /u01/app/

26. Configure environment for the oracle user.
    Set the default file mode creation mask (umask) to 022 in the shell startup file.Set the DISPLAY environment variable.
    umask 022

Next

You have finished installing Oracle and got the message that the installation completely successfully from the screen on OUI.

You will also see the path to the logs files from the installation. It is a good practice to review the logs for any errors.

Sometimes the installation may fail due to permission issues for example:

• The oracle user account you are using to install the database may not have permissions to write to the filesystem configured to house the oracle installation.
• The file system configured for the oracle installation files may be full.
• The system may have a missing prerequisite for the oracle install.

You will usually get an error message from the logs or displayed on the installation screen which will give you a clue of what the issue is.

It is your responsibility to review the installation requirements, fix the errors and resume the installation.

Now let us head over to Module 3 to create the database.

Next

• Plan the database creation.
• Create a database by using the Database Configuration Assistant (DBCA).
• Create a database using a response file.
• Generate database creation scripts with the DBCA.
• Manage database design templates with the DBCA.
• Delete a database using DBCA.
• Perform additional tasks with the DBCA.

Next

It is important to plan how the logical storage structure of the database will affect system performance and various database management operations.

You will be working with the application to team to determine the following:

• How many disk drives ( SATA, ISCSI drives )you have available and the type of storage needed (If they have any special storage requirements).
• What will be the size of the database initially and how large is it projected to grow.
• How many tablespaces are needed initially and project for a year and 5 years (plan for growth)
• How datafiles will be needed and what type of disks will they be stored on. The physical location of frequently accessed data dramatically affects application performance.
• What is the purpose of the database and what type of data will be stored there
• The overall database design.
• The backup and recovery policies and procedures.
• Information such as the availability of network attached storage (NAS) and the bandwidth for the private storage network are important. If storage area networks (SAN) are going to be used, knowing how the logical volumes are configured and the stripe size is useful.

Different types of databases have their own specific instance and storage requirements. Your Oracle database software includes templates for the creation of these different types of databases.

• General purpose: For general purpose or transaction processing usage such as working with transactions and storing them for a medium length of time
• Custom: For customized databases that do not fit into the general purpose or data warehouse template
• Data warehouse: For storing data for long periods and retrieving them in read operations.

Next

You can use the  Database Configuration Assistant (DBCA) to create your database. This is one of a few ways to create database.

You can also use DBCA to:

• Create database templates.
• Change the configuration of databases.
• Delete databases.
• Create a database from a list of predefined templates.
• Use an existing database as a sample to create a new database or template.

Follow the link to download the Steps To Create A Single Instance 12.2.1.0 Oracle Database On Linux

Steps To Create a Database On Linux

Next

A template is a predefined database definition which you can use as a starting point for a new database. You can either  create a template when you are creating a new database or launch DBCA to create it using Manage Templates option. Be sure to delete the templates when they are no longer needed.

There are three ways to create a template:

•  From an existing templateThis type of template is used to create a new database from pre-defined template settings. You have the flexibility of modifying the initialization parameters, storage parameters, or  use custom scripts.
• From an existing database (structure only)
  This type of template contains structural information from an existing database, including database options, tablespaces, datafiles, and initialization parameters. The application and user data is not copied over to the new database.
  You may frequently use this type of template to create a new database which has a similar to an existing database.
• From an existing database (structure as well as data)This is another way of cloning a database. This type of template is made of the structural information and physical datafiles of an database that already exists on the server. You can create a database from this type of template if you want to clone an existing database. The database is an exact replica of the database it was created from including the application and user data. This is only possible if the database

Note: Templates you create will appear in the Database Templates list when you create a new database using the DBCA.

Follow the link to download the Steps To Create A Single Instance 12.2.1.0 Oracle Database Template On Linux

Steps To Create A Database Template

Next

 In unit 6, you learned how to create a database template using DBCA. Templates are XML files ( eXtensible Markup Language) .

XML is a language used to store and move data over the internet.Templates are XML files that contain information required to create a database.

You can use templates to create new databases or clone existing databases. Cloning saves time by copying a seed database’s files to the correct locations.

Templates contains information such as  database options, initialization parameters, and storage attributes (for datafiles, tablespaces, control files, and online redo logs).

•  

  Templates are stored in the following directory:

  ORACLE_HOME/assistants/dbca/templates

  Benefits Of Using Templates

  • Time saving. If you use a template you do not have to define the database.
  • Easy Duplication. By creating a template containing your database settings, you can easily create a duplicate database without specifying parameters twice.
  • Easy editing. You can quickly change database options from the template settings.
  • Easy sharing. Templates can be copied from one machine to another.

   Types Of Templates

  Templates comprise of two types: seed and non seed templates.

  Seed Templates

  This type of template contains both the structure and the physical datafiles of an existing (seed) database. Your database starts as a copy of the seed database, and requires only the following changes:

  • Name of the database
  • Destination of the datafiles
  • Number of control files
  • Number of redo log groups
  • Initialization parameters

  Other changes can be made after database creation using custom scripts that can be invoked by DBCA, command-line SQL statements, or the Oracle Enterprise Manager.

  The datafiles and online redo logs for the seed database are stored in a compressed format in a file with a .dfj extension. The corresponding .dfj file’s location is stored in the .dbc file.

  Non Seed Templates (.dbt)

  This type of template is used to create a new database from scratch. It contains the characteristics of the database to be created. Non-seed templates are more flexible than their seed counterparts because all datafiles and online redo logs are created to your specification, and names, sizes, and other attributes can be changed as required.

  Oracle Database Templates

  The Oracle database comes bundled with the following templates:

  Data Warehouse

  Users perform numerous, complex queries that process large volumes of data. Response time, accuracy, and availability are key issues.

  These queries (SELECT statements) range from a fetch of a few records to queries that sort thousands of records from many different tables.

  Transaction Processing
  
  Many concurrent users perform numerous transactions that require rapid access to data. Availability, speed, concurrence, and recoverability are key issues.

  Transactions consist of reading (SELECT statements), writing (INSERT and UPDATE statements), and deleting (DELETE statements) data in database tables.

  General Purpose

  This template creates a database designed for general use. It combines features of both the DSS and OLTP database templates.

  Custom Database

  This template allows you maximum flexibility in defining a database.

  Follow the link to download the steps to create a database in Silent install from A response file

  Steps To Create A Database Using A Response FIle

Next

Scripts are a key part of any DBA’s life. Scripts are a good way to automate tasks that you do repetitively. Every good DBA has a tool box of scripts which they use for different tasks ( monitor server, create databases, monitor user sessions and processes, kill user processes, clone databases, check status of databases, backup databases, check listener status) and this saves a ton of time you would have used to manually type the script every time you want to perform specific tasks.

You want to use your time efficiently as a DBA. it is about  doing more quickly and with less effort . The most productive DBAs will attempt to script as much work as possible so it is reproducible and standardized.

As you begin your DBA career, you will notice that most DBAs you will encounter use scripts to perform most of their tasks. In most shops, they are standardized scripts which have already been created, approved and used to perform daily operations such as taking backups, archiving redo logs, rotating and compressing the listener logs, compressing the trace lines, deleting old archived redo logs after they have been moved to external storage devices such as tapes, starting and shutting down the databases, monitoring the databases, monitoring space and many more.

When you start working in a company, be sure to ask your team members where the scripts are stored and make time to go to that directory and review these scripts to understand what they are used for. You can also offer ideas for improving these scripts. Always ASK, ASK, ASK if you are unsure of a script before running it in any database environment.

And if a task is assigned to you and your coworkers give you a script that will support you in completing the task. DO NOT RUN the script without reviewing it.
Databases are also easily created via a series of scripts. Many companies have medium and large IT shops often have a set of standardized database-creation shell
scripts which they use to created their databases per use request

You will make a copy of the script which you will modify to enter the new database information It is best to make then script interactive, such that you are prompted to enter the database information.

This ensures that any DBA, regardless of skill will be able to jump right in and create a database on the fly to support  user their applications.
You can use the DBCA  to quickly generate your database creation scripts and then customize them to meet your organization’s needs. The set of scripts DBCA generates are Unix shell scripts that execute SQL*Plus scripts to issue the database-creation commands.

There is a master shell script that calls each of these scripts in order. Once the whole set is generated, you can use vi to edit certain scripts to reflect your desired database name, file structure, number of control files, number and size of online redo logs, and various other parameters.

Once this customization is done, you can execute the scripts to create the database.

This what the master shell script generated for the kuddev database:

Contents of kuddev.sql file

DBCA can either generate the database creation scripts in the default location shown on the screenshot above or you can enter a path to store the scripts.

The default location for these scripts is $ORACLE_BASE/ORACLE_SID/scripts.

Next

The three main Oracle Networking files you must know are:

Tnsnames.ora
Listener.ora
Sqlnet.ora

Let’s briefly review each of them.

Tnsnames.ora File

The tnsnames.ora file is a client-side file. This means that it is located on the client machines. It is also located on the server to make connections to databases from other servers.

When you start an SQL*Plus session to connect to a database and you enter a connect string such as

The tnsnames.ora file on your machine is examined to determine whether the database kuddev exists. If the database exist, a connection is attempted with the server specified on the specified port. The username/password is authenticated and if it is valid, a database connection is established.

If the value is not found, an Oracle message reports that the tnsnames could not resolve the service name (ORA-12154: TNS:could not resolve service name).

You recall the two types of connections you learned in the Relational Database Concepts course:

• Local database connections.
• Remote database connections

For Local connections:

• You connect to your database locally ( by starting and SQL*Plus connection on the server hosting the database)
• Enter your ORACLE_SID for the database you want to connect to.
• Connect with your username/password at the sqlplus prompt.
• No need to database with the @database_name.
• You connect directly to the server via the bequeath protocol.
• The listener does not need to be running in order for you to be connect to the database.local connection is when you are already logged into the database server, your ORACLE_SID is set, and you do not specify the database in your connect string:
  $ sqlplus system
  A local connection will use the bequeath protocol and does not require the listener to be running.
  Typically, this is how most DBAs connect to the database because they are already logged onto the linux box from an SSH client.For Remote Connections
  A remote connection involves the user connecting from a client machine to the database server. This requires the use of a connect string specifying the target database and a tnsnames.ora on their machine. The listener process must also be running on the database server. This is the type of connection used by most end users. An attempt to connect to a remote database looks like this:

When you create a new database, the entry for this database has to be entered into the tnsnames.ora file. Note that if the database was created using DBCA, the tns entry for the database will automatically be updated in the tnsnames.ora file.

You will also send a copy of the tns entry for the database to your database users when you send out theur database credentials.They will then add this entry to the tnsnames.ora on their machines.

Most companies have a Master Copy of the tnsnames.ora file with all the entries of the databases included and this file is maintained and updated frequently as new databases are created, old databases deleted, current databases renames.

Below is the tnsnames.ora file from the kudrant server.

Listener.ora File

This is what the listener.ora file looks like on the kudrant server.

The listener is a background process that listens for incoming connection requests and then passes those requests to the appropriate database.

if you look at the listener.ora file above, you can note that:

• A port is specified the port where the vlaid connections will come in. Your connections
  will initially
• A protoc is specified, in our case, it is the TCP.
• The service name to connect to the database.The listener.ora file like the tnsnames.ora files resides in the $ORACLE_HOME/network/admin directory on the linux machine.
  [69-64-90-149:oracle]$ls
  listener.ora  sqlnet.ora tnsnames.ora

  When you create a new database manually, you will copy the following lines and past into the listener.ora file.(SID_DESC =
  (GLOBAL_DBNAME = kuddev.phx.dedicated.codero.com)
  (ORACLE_HOME = /home/oracle/app/oracle/product/12.2.0/dbhome_1)
  (SID_NAME = kuddev)
  )Be sure to register the listener with the database.Note that the listener process must be up and running in order to listen to incoming database connections from other machines from the clients or applications.If the listener process is not running, you will be able to connect to the database locally, but outside connections to your database will fail.Only ONE listener is configured per database server for a specific port.

  What does this mean?

  This means that only one listener process is required to support all the databases on a server regardless whether you have multiple versions of the oracle database running or not if all these databases are configured to listen on thesame port. The default port for the database is 1521.

  I have worked in companies where mutliple listeners where running from the same database server because some of these database contained very sensitive data were secured behind firewalls. Thus, the port 1521 was disallowed and other secure ports had to be configured for the listener to listen from.

   

   Check The Status Of The Listener

  You can if the listener process is running by typing the following command.

  [69-64-90-149:oracle]$ps -ef|grep LIS
  oracle 18738 1 0 Mar21 ? 00:00:12 /home/oracle/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr KUD_LISTENER -inherit

  You can also check the status of the listener by using the LSNRCTL STATUS LISTENER_NAME command.

  [69-64-90-149:oracle]$lsnrctl status KUD_LISTENER

  LSNRCTL for Linux: Version 12.2.0.1.0 – Production on 07-APR-2017 16:53:19

  Copyright (c) 1991, 2016, Oracle. All rights reserved.

  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=69-64-90-149.phx.dedicated.codero.com)(PORT=1529)))
  STATUS of the LISTENER
  ————————
  Alias KUD_LISTENER
  Version TNSLSNR for Linux: Version 12.2.0.1.0 – Production
  Start Date 21-MAR-2017 18:49:34
  Uptime 16 days 22 hr. 3 min. 48 sec
  Trace Level off
  Security ON: Local OS Authentication
  SNMP OFF
  Listener Parameter File /home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
  Listener Log File /home/oracle/app/oracle/diag/tnslsnr/69-64-90-149/kud_listener/alert/log.xml
  Listening Endpoints Summary…
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=69-64-90-149.phx.dedicated.codero.com)(PORT=1529)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1529)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=69-64-90-149.phx.dedicated.codero.com)(PORT=5500))(Security=(my_wallet_directory=/home/oracle/app/oracle/admin/kuddev/xdb_wallet))(Presentation=HTTP)(Session=RAW))
  Services Summary…
  Service “kuddev.phx.dedicated.codero.com” has 1 instance(s).
  Instance “kuddev”, status READY, has 1 handler(s) for this service…
  Service “kuddevXDB.phx.dedicated.codero.com” has 1 instance(s).
  Instance “kuddev”, status READY, has 1 handler(s) for this service…
  The command completed successfully

  Start the Listener
  
  You can start the listener by using the LSNRCTL START LISTENER_NAME command. Always verify that the listener is up after the start it.

  [69-64-90-149:oracle]$lsnrctl start KUD_LISTENER

  LSNRCTL for Linux: Version 12.2.0.1.0 – Production on 07-APR-2017 17:05:12

  Copyright (c) 1991, 2016, Oracle. All rights reserved.

  Starting /home/oracle/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr: please wait…

  TNSLSNR for Linux: Version 12.2.0.1.0 – Production
  System parameter file is /home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
  Log messages written to /home/oracle/app/oracle/diag/tnslsnr/69-64-90-149/kud_listener/alert/log.xml
  Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=69-64-90-149.phx.dedicated.codero.com)(PORT=1529)))
  Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1529)))

  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=69-64-90-149.phx.dedicated.codero.com)(PORT=1529)))
  STATUS of the LISTENER
  ————————
  Alias KUD_LISTENER
  Version TNSLSNR for Linux: Version 12.2.0.1.0 – Production
  Start Date 07-APR-2017 17:05:12
  Uptime 0 days 0 hr. 0 min. 0 sec
  Trace Level off
  Security ON: Local OS Authentication
  SNMP OFF
  Listener Parameter File /home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
  Listener Log File /home/oracle/app/oracle/diag/tnslsnr/69-64-90-149/kud_listener/alert/log.xml
  Listening Endpoints Summary…
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=69-64-90-149.phx.dedicated.codero.com)(PORT=1529)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1529)))
  The listener supports no services
  The command completed successfully

  After you register your newly created database with the listener, be sure to check that you can see thedatabase service when your run the lsnrctl status command.

  Every time you modify the listener.ora file, you have to restart/reload or simple in DBA lingo BOUNCE the listener for the changes to take effect.

  You can either stop and restart the listener or reload it.

  [69-64-90-149:oracle]$lsnrctl reload KUD_LISTENER

  LSNRCTL for Linux: Version 12.2.0.1.0 – Production on 07-APR-2017 17:11:23

  Copyright (c) 1991, 2016, Oracle. All rights reserved.

  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=69-64-90-149.phx.dedicated.codero.com)(PORT=1529)))
  The command completed successfully

  Stop the listener

   

  You can stop the listener by using the LSNRCTL STOP LISTENER_NAME command. Always verify that the status of the listener after you make any changes.

  [69-64-90-149:oracle]$lsnrctl stop KUD_LISTENER

  LSNRCTL for Linux: Version 12.2.0.1.0 – Production on 07-APR-2017 17:04:26

  Copyright (c) 1991, 2016, Oracle. All rights reserved.

  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=69-64-90-149.phx.dedicated.codero.com)(PORT=1529)))
  The command completed successfully
  [69-64-90-149:oracle]$ps -ef|grep LIS
  [/box]

  Don’t forget to restart the listener after you have stopped it after you bounce it. Often times, DBAs including myself have forgotten to restart the listener after it was bounced.  And of course to hear queries from users asking why they cannot complain to the database.

  it is very important to CHECK CHECK CHECK everything ….meaning the status of the databases, listener, agents before you notify the users that the database is available for use.

sqlnet.ora File

Next

You have successfully created your brand new database to support an application.

Now what?

Are you just going to turn it over to the application Team? No.

You have to configure it to meet the basic needs of the application. Most often the requirements for the database such as memory, size, processes, characterset would have been stated in the ticket created for the request. Some of these parameters you would have already configured while you created the database.

Below are some important database post installation tasks you will need to configure before you turn it over to the users.

1.  Passwords Of Administrative Accounts Change the passwords for the administrative accounts such as SYS , SYSTEM, DBSNMP. You can do this logging into the SQL*Plus and issuing the command
   SQL>alter user sys identified by “password”;
   SQL>!Clear

   Since passwords are very sensitive, it maybe better to use a script if it is available to change these passwords. You can also enter the passwords during the database creation process.

2. Account Management: Lock all the standard accounts which come bundled with the oracle installation if they are not needed. You can check these accounts by using the statement below. Your company will have a guide as to whic accounts should be locked when new databases are create and configured. Be sure to review these.
   select username from dba_users order by created;
   SQL> alter user scott account lock;
3. Create User AccountsKeep in mind that a database does not exist on its. It’s purpose is to support applications. usually the database request comes with the names of the user accounts that you have to configure including the permissions on these accounts. You will also create a DBA account for access to the database by the DBAs.
   create user userdba identified by password
   default tablespace users
   temporary tablespace temp
   password expire;grant connect,resource,dba to userdba;
4. Security Script: Many shops have a security script configured including all the security controls for the database and you will implement this script  for every database you create.
5. Oracle Networking Files: Configure the oracle networking files (listener.ora, tnsnames.ora, sqlnet.ora). If you manually created a database,  be sure to add the tns entry for this database  in the tnsnames.ora and listener.ora files.If you are creating the database in a brand new server and  you have a working listener.ora and tnsnames.ora files from another server, you can copy them to your new server and edit them for the new databases.If you used the DBCA to create your database, you will notice that the network files are automatically configured. These files usually reside in the $ORACLE_HOME/network/admin directory.You can also create your tnsnames.ora and listener.ora file by using the Network Configuration Assistant tool that come bundled with the oracle software installations. These tools are located in the $ORACLE_HOME/bin directories.
6. Register the database with the listener You will register your newly created database so that your users can connect remotely and perform their tasks.
   SQL>alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=REGISTER)))’ scope = both;
   SQL>alter system register;
7. Configure the database in archive log mode You will configure you new database in archive log mode especially if it is a production database by following the steps below.
8. Configure backups Configure the database for backups based on your company’s backup policies and procedures.
9. Set up Monitoring Add the database target to Oracle Enterprise Manager Cloud Control for monitoring.All the companies I have worked at, use this tool to administer and monitor their databases.
10. Edit the oratab File
    
    The oratab file contains a listing of each database, its ORACLE_HOME, and a Y|N flag to determine whether the database should be automatically restarted on machine reboot. It is used to determine which databases are on a server, their versions, and if they should be automatically started.Shell scripts often use this file when they set up a user’s login environment.The oratab file is located in the  /etc directory on Linux platforms and in the /var/opt/oracle directory on Sun Solaris platforms.If you create a database completely with DBCA or use DBCA to generate scripts, the oratab file will be automatically updated.If you created and ran the scripts yourself, you need to add a line to this file containing your new database.
    This is what the oratab file looks like on the servers:
    # This file is used by ORACLE utilities. It is created by root.sh
    # and updated by either Database Configuration Assistant while creating
    # a database or ASM Configuration Assistant while creating ASM instance.# A colon, ‘:’, is used as the field terminator. A new line terminates
    # the entry. Lines beginning with a pound sign, ‘#’, are comments.
    #
    # Entries are of the form:
    # $ORACLE_SID:$ORACLE_HOME:<N|Y>:
    #
    # The first and second fields are the system identifier and home
    # directory of the database respectively. The third field indicates
    # to the dbstart utility that the database should , “Y”, or should not,
    # “N”, be brought up at system boot time.
    #
    # Multiple entries with the same $ORACLE_SID are not allowed.
    #
    #
    kuddev:/home/oracle/app/oracle/product/12.2.0/dbhome_1:N
    kudtst:/home/oracle/app/oracle/product/12.2.0/dbhome_1:N
    kudprd:/home/oracle/app/oracle/product/12.2.0/dbhome_1:N
11. Customizing Your .profile File Configure the environmental variables for oracle if has not yet been configured ..meaning that the server is a brand new server.

Next

After you successfully install Oracle Database:

• The database that you created and the default Oracle Net listener process run on the system.
• Oracle Enterprise Manager Database Control run on the system and can be accessed by using a web browser.

Next

Follow the link to download the Steps To Delete a Database Using DBCA

Steps To Delete a Database Using DBCA

Note: The database being deleted must be up and running since the DBCA has to be able to connect to it to determine file location information.

Dropping/deleting a database involves removing its:

• Data files
• Redo log files,
• Control files and
• Initialization parameter files.You can also remove a database manually by using the DROP DATABASE SQL statement.  When you issue the DROP DATABASE statement, it deletes all control files and all other database files listed in the control file.

To use the DROP DATABASE statement successfully:

• The database must be mounted and closed.
• The database must be mounted exclusively (not in shared mode).
•  The database must have been started in RESTRICT mode.

Steps To Delete A Manually Database From SQL*Plus

1. Check the locations of all the database files
   [69-64-90-149:oracle]$export ORACLE_SID=kudprd
   [69-64-90-149:oracle]$sqlplus / as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Tue Apr 4 06:00:51 2017Copyright (c) 1982, 2016, Oracle. All rights reserved.
   Connected to:
   Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit ProductionSQL> select * from global_name;GLOBAL_NAME
   ——————————————————————————–
   KUDPRD.PHX.DEDICATED.CODERO.COMSQL> SELECT name from v$controlfile;NAME
   ——————————————————————————–
   /home/oracle/app/oracle/oradata/KUDPRD/control01.ctl
   /home/oracle/app/oracle/fast_recovery_area/KUDPRD/control02.ctlSQL> SELECT member FROM v$logfile;MEMBER
   ——————————————————————————–
   /home/oracle/app/oracle/oradata/KUDPRD/redo01.log
   /home/oracle/app/oracle/oradata/KUDPRD/redo02.log
   /home/oracle/app/oracle/oradata/KUDPRD/redo03.logSQL> SELECT name FROM v$datafile;NAME
   ——————————————————————————–
   /home/oracle/app/oracle/oradata/KUDPRD/system01.dbf
   /home/oracle/app/oracle/oradata/KUDPRD/sysaux01.dbf
   /home/oracle/app/oracle/oradata/KUDPRD/undotbs01.dbf
   /home/oracle/app/oracle/oradata/KUDPRD/users01.dbf
2. Shutdown the database
   SQL> shutdown immediate;
   Database closed.
   Database dismounted.
   ORACLE instance shut down.
3. Start the database in restrict mode.
   SQL> startup mount exclusive restrict
   ORACLE instance started.Total System Global Area 2449473536 bytes
   Fixed Size 8623640 bytes
   Variable Size 671091176 bytes
   Database Buffers 1761607680 bytes
   Redo Buffers 8151040 bytes
   Database mounted.
4. Drop the database.

   SQL> drop database;Database dropped.Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

5. Check to see if the database files have been removed.
   [69-64-90-149:oracle]$cd /home/oracle/app/oracle/oradata/KUDPRD
   [69-64-90-149:oracle]$ls
   [69-64-90-149:oracle]$

The DROP DATABASE statement has no effect on archived log files, nor does it have any effect on copies or backups of the database. It is best to use Recovery Manager (RMAN) to delete such files.

The preferred method for dropping a database is by using DBCA.

Next

After completing this lesson, you should be able to:

• Start and stop the Oracle database and components
•  Use Oracle Enterprise Express
• Access a database with SQL*Plus
• Modify database initialization parameters
•  Learn the stages of database startup
•  Learn database shutdown options
• View the alert log
• Access dynamic performance views

Next

When you start the instance, an initialization parameter file is read. There are two types of parameter files.

• Server parameter file (SPFILE)This is the first file the oracle server will look for upon startup. It is a binary file and this means that you cannot open it up using a text editor to edit.If you try to do that, it will get corrupted and you will have to recreate it.The default name of this file is spfile<SID>.ora where the SID refers to the name of the oracle instance.The spfile<SID>.ORA file is written to and read by the database server and  and  like the name indicated, it is a server-side file meaning that it  resides on the server on which the Oracle instance is executing.On your Linux box, it is resides in the $ORACLE_HOME/dbs directory as shown below:
  [69-64-90-149:oracle]$cd $ORACLE_HOME/dbs
  [69-64-90-149:oracle]$ls
  hc_kuddev.dat init.ora lkKUDTST orapwkudtst
  hc_kudprd.dat lkKUDDEV orapwkuddev spfilekuddev.ora
  hc_kudtst.dat lkKUDPRD orapwkudprd spfilekudtst.ora

  The spfile is persistent across shutdown and startup.

  If you want to edit the spfile<SID>.ora file, you have to create a text version of it  and update that.

  SQL> create pfile from spfile;

  File created.

• Text initialization parameter fileDuring the startup of the instance, Oracle searches for the spfile<SID>.ora and if it does not find it, it will look for the spfile.ora.It it does not find the spfile.ora, then it will search for the init<SID>.ora file.The init<SID>.ora  or initialization parameter file can be read by the database server if the spfile is not located, but oracle does not write to it.The initialization parameter settings must be set and changed manually by using a text editor so that they are persistent across shutdown and startup.
  This means that any changes made to this file will all take effect after the database has been restarted.Just like the spfile<SID>.ora, the init<SID>.ora file is located in the $ORACLE_HOME/dbs directory.

It is recommended that you create an SPFILE as a dynamic way to maintain initialization parameters.

The Oracle database server using  values  such as Boolean, string, integer, big integer, parameter file and reserved for initialization parameters.

There are different types of parameter values:

Derived Parameter Values

Derived  initialization parameters are those whose values are calculated from the values of other parameters.

It is not best practice to modify values for derived parameters because  the value that you specify overrides the calculated value.

Examples of derived parameter

SESSIONS – The sessions parameter is derived from the value of the PROCESSES parameter. If you increase the PROCESSES parameter in the dataabse,  the default value of SESSIONS changes as well unless you override it with a specified value.

Operating System–Dependent Parameter Values

The valid values or value ranges of some initialization parameters depend on the host operating system. For example, the DB_FILE_MULTIBLOCK_READ_COUNT parameter specifies the maximum number of blocks that are read in one I/O operation during a sequential scan; this parameter is platform dependent. The size of those blocks, which is set by DB_BLOCK_SIZE, has a default value that depends on the operating system.

Setting Parameter Values

Initialization parameters offer the most potential for improving system performance. Some parameters set capacity limits but do not affect performance. For example, when the value of OPEN_CURSORSis 10, a user process attempting to open its eleventh cursor receives an error. Other parameters affect performance but do not impose absolute limits. For example, reducing the value of OPEN_CURSORSdoes not prevent work even though it may slow the performance.

Increasing the values of parameters may improve your system’s performance, but increasing most parameters also increases the system global area (SGA) size. A larger SGA can improve database performance up to a point. In virtual memory operating systems, an SGA that is too large can degrade performance if it is swapped in and out of memory. Operating system parameters that control virtual memory working areas should be set with the SGA size in mind. The operating system configuration can also limit the maximum size of the SGA.

Simplified Initialization Parameters

Initialization parameters are of two types: basic and advanced.

In the majority of cases, it is necessary to set and tune only the 30 basic parameters to get reasonable performance from the database. In rare situations, modification of the advanced parameters may be needed to achieve optimal performance. There are about 314 advanced parameters.

A basic parameter is defined as one that you are likely to set to keep your database running with good performance. All other parameters are considered to be advanced.

Examples of basic parameters:

• Determining the global database name: DB_NAME and DB_DOMAIN
• Specifying a fast recovery area and size: DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE
• Specifying the total size of all SGA components: SGA_TARGET
• Specifying the method of undo space management tablespace: UNDO_TABLESPACE
• COMPATIBLE initialization parameter and irreversible compatibility

More Examples of Initialization Parameters

CONTROL_FILES parameter: Specifies one or more control file names. Oracle strongly recommends that you multiplex and mirror control files. Range of values: from one to eight file names (with path names).

DB_FILES parameter: Specifies the maximum number of database files that can be opened for a database. The default value is 200.

PROCESSES parameter: Specifies the maximum number of OS user processes that can simultaneously connect to an Oracle server. This value should allow for all background processes and user processes.The range of values is from 6 to an OS-dependent value and the default value is 150 in oracle 11g and 300 in oracle 12c.

DB_BLOCK_SIZE parameter: Specifies the size (in bytes) of an Oracle database block. This value is set at database creation and cannot be subsequently changed. This specifies the standard block size for the database. All tablespaces will use this size by default. Range of values: 2048 to 32768 (OS dependent). Default value: 8192 .

DB_CACHE_SIZE parameter: Specifies the size of the standard block buffer cache. Range of values: at least 16 MB. Default value: 0 if SGA_TARGETis set, otherwise the larger of 48 MB or (4 MB*cpu_count).

SGA_TARGET specifies the total size of all SGA components. If SGA_TARGETis specified, the following memory pools are automatically sized:

• Buffer cache (DB_CACHE_SIZE)
• Shared pool (SHARED_POOL_SIZE) • Large pool (LARGE_POOL_SIZE)
• Java pool (JAVA_POOL_SIZE)
• Streams pool (STREAMS_POOL_SIZE)

If these automatically tuned memory pools are set to nonzero values, the values are used as minimum levels by Automatic Shared Memory Management (ASMM). You set minimum values if an application component needs a minimum amount of memory to function properly.

MEMORY_TARGET specifies the Oracle system wide usable memory. The database tunes memory to the MEMORY_TARGET value, reducing or enlarging the SGA and PGA as needed.

In a text-based initialization parameter file, if you omit MEMORY_MAX_TARGET and include a value for MEMORY_TARGET, the database automatically sets MEMORY_MAX_TARGE Tto the value of MEMORY_TARGET. If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, the MEMORY_TARGET parameter defaults to zero. After startup, you can then dynamically change MEMORY_TARGE Tto a nonzero value if it does not exceed the value of MEMORY_MAX_TARGET. The MEMORY_TARGET parameter is modifiable with the ALTER SYSTEM command. Values range from 152 MB to MEMORY_MAX_TARGET.

PGA_AGGREGATE_TARGET parameter: Specifies the amount of Program Global Area (PGA) memory allocated to all server processes attached to the instance. This memory does not reside in the System Global Area (SGA). The database uses this parameter as a target amount of PGA memory to use. When setting this parameter, subtract the SGA from the total memory on the system that is available to the Oracle instance. The range of values comprises integers plus the letters K, M, or G (to specify this limit in kilobytes, megabytes, or gigabytes). The minimum value is 10 MB and the maximum value is (4096 GB – 1). The default is 10 MB or 20% of the size of the SGA, whichever is greater.

SHARED_POOL_SIZE parameter: Specifies the size of the shared pool in bytes. The shared pool contains objects such as shared cursors, stored procedures, control structures, and parallel execution message buffers. Range of values: OS dependent. Default value: 0 if SGA_TARGETis set, otherwise 128 MB if 64 bit; 48 MB if 32 bit.

UNDO_MANAGEMENT parameter: Specifies the undo space management mode that the system should use. When set to AUTO, the instance is started in Automatic Undo Management (AUM) mode. Otherwise, it is started in Rollback Undo (RBU) mode. In RBU mode, undo space is allocated externally as rollback segments. In AUM mode, undo space is allocated externally as undo tablespaces. Range of values: AUTO or MANUAL. If the UNDO_MANAGEMENT parameter is omitted when the first instance is started, the default value AUTOis used.

Using SQL*Plus to View Parameters

You can query the data V$PARAMETER  view to find the values of the various parameters. V$PARAMETER displays the current parameter values in the current session. You can also use the SHOW PARAMETER command with any string to view parameters that contain that string.

The query in the following example is requesting the name and values of the parameters. Use a WHERE clause to specify specific parameter names:

SQL> SELECT name, value FROM V$PARAMETER WHERE name LIKE ‘%pool%’;

Next

There are two types of initialization parameters:

• Static parameters
• Dynamic parameters

Static parameters

Affect the instance or entire database and can be modified only by changing the contents of the init.oraor the SPFILE. Static parameters require the database to be shut down and restarted to take effect. They cannot be changed for the current instance.

Dynamic parameters

Can be changed while database is online. There are two types:

• Session-level parameters affect only a user session. Examples include national language support (NLS) parameters that can be used to specify national language settings for sorts, date parameters, and so on. You can use these in a given session; they expire when the session ends. Follow the link to see examples of parameters which could be enabled at the session level in the database
• System-level parameters affect the entire database and all sessions. Examples include modifying the SGA_TARGET value and setting archive log destinations. These parameters stay in effect based on the SCOPEspecification. To make them permanent, you have to add these parameter settings to the SPFILE by specifying the SCOPE=both option or manually editing the PFILE.

Dynamic parameters can be changed by using the ALTER SESSION and ALTER SYSTEM commands.

you can query the v$parameter view to see the parameter

SQL> desc v$parameter

The second example shows the use of the SQL*Plus SHOW PARAMETERcommand to view parameter settings. You can also use this command to find all parameters that contain a text string. For example, you can find all parameter names that include the string db by using the

SQL> sho parameter db_file

Query to process view to see the number of processes defined for the database

Query to db_name view to see the database name

Query to db_block view to see value of the data block size

Other Views Containing Information About Parameters

• V$SPPARAMETER: Displays information about the contents of the server parameter file. If a server parameter file was not used to start the instance, each row of the view will contain FALSE in the ISSPECIFIED column.
• V$PARAMETER2: Displays information about the initialization parameters that are currently in effect for the session, with each parameter value appearing as a row in the view. A new session inherits parameter values from the instance-wide values displayed in the V$SYSTEM_PARAMETER2view.
• V$SYSTEM_PARAMETER: Displays information about the initialization parameters that are currently in effect for the instance.

Steps to change Initialization Parameter Values

Use the SET clause of the ALTER SYSTEM statement to set or change initialization parameter values. The optional SCOPEclause specifies the scope of a change as follows:

• SCOPE=SPFILE: The change is applied in the server parameter file only. No change is made to the current instance. For both dynamic and static parameters, the change is effective at the next startup and is persistent. This is the only SCOPE specification allowed for static parameters.
• SCOPE=MEMORY: The change is applied in memory only. The change is made to the current instance and is effective immediately. For dynamic parameters, the effect is immediate but not persistent because the server parameter file is not updated. For static parameters, this specification is not allowed.
• SCOPE=BOTH: The change is applied in both the server parameter file and memory. The change is made to the current instance and is effective immediately. For dynamic parameters, the effect is persistent because the server parameter file is updated. For static parameters, this specification is not allowed.

It is an error to specify SCOPE=SPFILE or SCOPE=the instance did not start up with a server parameter file. The default is SCOPE=BOTH if a server parameter file was used to start up the instance, and the default is MEMORY if a text initialization parameter file was used to start up the instance.

For some dynamic parameters, you can also specify the DEFERRED keyword. When it is specified, the change is effective only for future sessions. This is only valid for the following parameters:

• backup_tape_io_slaves
•  recyclebin
• audit_file_dest
• object_cache_optimal_size
• object_cache_max_size_percent
• sort_area_size
• sort_area_retained_size
• olap_page_pool_size

When you specify SCOPE as SPFILE or as BOTH, an optional COMMENT clause lets you associate a text string with the parameter update. The comment is written to the server parameter file.

Next

You can start the database in different states and will will learn that in this unit.

Recall that the instance is just a collection of memory structures and background processes that allows us to tap into a database.

An instance is typically started only in NOMOUNT mode during database creation, during re-creation of control files, or during certain backup and recovery scenarios.

During instance startup, oracle will go the $ORACLE_HOME/dbs directory and perform the following tasks:

• Look for a file called spfileSID.ora. For example of your instance name is kuddev, it will look for the spfilekuddev.ora.
• if the spfileSID.ora parameter file is not in the directory, it will look for the spfile.ora.
• If the spfile.ora file is not found, it will look for the initSID.ora file.
• Allocating the SGA
• Starting the background processes
• Opening the alert_<SID>.logfile and the trace files

This is the file that contains initialization parameters for the instance. Specifying the PFILE/SPFILE parameter during STARTUP overrides the default behavior.

Note: SIDis the system ID, which identifies the instance name. For the kuddev database, it will be kuddev.

There are three STARTUP modes:

• Startup
• Startup NOMOUNT
• Startup MOUNT

Starting Up an Oracle Database Instance: MOUNT

Mounting a database includes the following tasks:

• Associating a database with a previously started instance
• Locating and opening all of the control files specified in the parameter file
• Reading the control files to obtain the names and statuses of the data files and online redo log files (However, no checks are performed to verify the existence of the data files and online redo log files at this time.)

To perform specific maintenance operations, start an instance and mount a database, but do not open the database.

For example, the database must be mounted but must not be opened during the following tasks:

• Renaming data files (Data files for an offline tablespace can be renamed when the database is open.)
• Enabling and disabling online redo log file archiving options
• Performing full database recovery

Note: A database may be left in MOUNT mode even though an OPEN request has been made.

Starting Up an Oracle Database Instance: OPEN

A normal database operation means that an instance is started and the database is mounted and opened. With a normal database operation, any valid user can connect to the database and perform typical data access operations.

Opening the database includes the following tasks: •

• Opening the data files
• Opening the online redo log files

If any of the data files or online redo log files are not present when you attempt to open the database, the Oracle server returns an error.

During this final stage, the Oracle server verifies that all data files and online redo log files can be opened, and checks the consistency of the database. If necessary, the System Monitor (SMON) background process initiates instance recovery.

You can start up a database instance in restricted mode so that it is available to users with administrative privileges only. To start an instance in restricted mode, select the “Restrict access to database” option on the Advanced Startup Options page.

Syntax for Startup modes

• STARTUP – To start the instance, associates the database files to it, and mounts and opens the database.
• STARTUP NOMOUNT – To start the instance and the database is not mounted.
• ALTER DATABASE MOUNT; – To mount a database from the NOMOUNT state.
• ALTER DATABASE OPEN; – To open the database from the MOUNTstate

When you start a database, you can specify which state it will open to. The default is open (STARTUP), but you can specify STARTUP NOMOUNT or STARTUP MOUNT depending on which tasks you need to perform. The database does not open directly to any one particular stage; it progresses from the nomount to mount to the open stage. If any problems are encountered along the way, the process stops and an error message is issued.

Steps to start a database:

1. Log in as oracle.
2. Set your ORACLE_SID to the instance you want to start.
3. Verify your ORACLE_HOME
4. Run the .profile file to set your environment variables for the ORACLE_HOME.
   . .profile
5. Set your ORACLE_SID to the instance you want to start.[69-64-90-149:oracle]$export ORACLE_SID=kudtst
6. Connect to the database from sqlplus  as sysdba.
7. dd

7. Start the database in nomount mode.

8. If you start the database in mount or nomount mode, you can continue to open the database
from that stage:

9. Open the database.

Start the database in mount mode.

Another handy startup option is STARTUP RESTRICT. When the database is started in restricted session mode, only users (such as those with roleDBA) with the system privilege RESTRICTED SESSION can connect to the database. Use this when you are performing maintenance or want only certain users to be able to log in.

If the database is already open, you can set it to restricted session even if normal users are logged in (no new users will be able to log in):

Once your task is completed, you can open the system to allow regular users to log in.

Oracle will, by default, use the init.ora specified in the $ORACLE_HOME/dbs directory. This is typically a database link to the init.ora in the $ORACLE_BASE/admin/oracle_SID/pfile directory. You can override this default by specifying a PFILE parameter on the startup line.
Use this method when there is a special init.ora file you need to use. For example:SQL> startup pfile=’$ORACLE_BASE/admin/kuddev/pfile/initkuddev.ora’

Next

The oracle diagnostic files are the alert log and trace files

Alert logs

Each database has an alert_<sid>.log file.

This file resides on the server and is database specitic. It is stored in

by default if $ORACLE_BASE is set.

For example in the kuddev database, this file is locaed in the $ORACLE_BASE/diag/rdbms/kuddev/kuddev/trace

The alert log  file or simply alert log  of a database is a chronological log of messages such as the following:

• All internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60) that occurred in the database.
• Any non default initialization parameters used at startup.
• Administrative operations, such as the SQL statements CREATE, ALTER, DROP DATABASE, and TABLESPACE, and the Enterprise Manager or SQL*Plus statements STARTUP, SHUTDOWN, ARCHIVELOG, and RECOVER.
• Several messages and errors relating to the functions of shared server and dispatcher processes.
• Errors during the automatic refresh of a materialized view

The alert log keeps a record of these events as an alternative to displaying the information on an operator’s console. If an administrative operation is successful, a message is written in the alert log as “completed” along with a time stamp.

Oracle Enterprise Manager monitors the alert log file and displays the critical errors. You can also view the log to see noncritical error and information messages.

The alert log  can grow to an unmanageable size since it is continuously written to. It is a good practice to periodically back up the alert file and delete the current alert file. When the database attempts to write to the alert file again, it re-creates a new one.

On most of the projects i have supported, a cron job is configured to backup and rotate the alert logs monthly.

The alert log is written as both an XML-formatted file and as a text file. Both these log files are stored inside the ADR home.

The ADR root directory is known as ADR BASE. The Automatic Diagnostic Repository (ADR) is a directory structure that is stored outside of the database.This parameter is set by DIAGNOSTIC_DEST initialization parameter.

If this parameter is omitted or left null, the database sets DIAGNOSTIC_DEST upon startup as follows:

• If environment variable ORACLE_BASE is set, DIAGNOSTIC_DEST is set to the directory designated by ORACLE_BASE.
• If environment variable ORACLE_BASE is not set, DIAGNOSTIC_DEST is set to ORACLE_HOME/log.
  SQL> sho parameter diagNAME                                 TYPE        VALUE
  ————————— ———– ——————————
  diagnostic_dest                      string      /home/oracle/app/oracle

Note: There is an XML version of the alert log in the

directory.

For example for the kuddev database, the xml version of the alert log is located in the $ORACLE_BASE/diag/rdbms/kuddev/kuddev/alert

Next

Data dictionary views and dynamic performance views (v$) are views used by oracle to perform administrative tasks such as performance tuning and monitoring.

You may asked to distinguish  between data dictionary views and dynamic performance views during your interview and it is important to understand the difference between the two views.

The Oracle data dictionary is the metadata of the database and contains the names and attributes of all objects in the database. The creation or modification of any object causes an update to the data dictionary that reflects those changes. This information is stored in the base tables that are maintained by the Oracle database, but you access these tables by using predefined views rather than reading the tables directly.

The data dictionary:

• Is used by the Oracle database server to find information about users, objects, constraints, and storage
• Is maintained by the Oracle database server as object structures or definitions are modified • Is available for use by any user to query information about the database
• Is owned by the SYS user
• Should never be modified directly using SQL

Note: The DICTIONARY data dictionary view (or the DICT synonym for this) contains the names and descriptions of data dictionary tables and views. Use the DICT_COLUMNS view to see the view columns and their definitions. For complete definitions of each view, see the Oracle Database Reference. There are over 1000 views that reference hundreds of base tables.

The view prefixes indicate the data (and how much of that data) a given user can see.

The global view of everything is accessed only by users with DBA privileges, using the DBA_prefix.

The next level of privilege is at the ALL_prefix level, which represents all objects that the querying user is privileged to see, whether the user owns them or not. For example, if USER_Ahas been granted access to a table owned by USER_B, then USER_Asees that table listed in any ALL_view dealing with table names.

The USER_prefix represents the smallest scope of visibility. This type of view shows only those objects that the querying user owns (that is, those that are present in the user’s own schema).

Generally, each view set is a subset of the higher-privileged view set, row-wise and column-wise. Not all views in a given view set have a corresponding view in the other view sets. This is dependent on the nature of the information in the view. For example, there is a DBA_LOCK view, but there is no ALL_LOCK view. This is because only a DBA would have

interest in data about locks. Be sure to choose the appropriate view set to meet the need that you have. If you have the privilege to access the DBA views, you still may want to query only the USER version of the view because the results show information on objects that you own and you may not want other objects to be added to your result set.

The DBA_views can only be queried by users with the SYSDBA or SELECT ANY DICTIONARY privilege.

Not all dictionary views start with the prefix DBA_, ALL_, and USER_. The following views or synonyms to views are exceptions to this:

• Data dictionary views are views built on a set of data dictionary tables. Data dictionary tables are created automatically the first time a database is created. Session is established whenever an user logs into the database.
• Dynamic performance views exist to track session activities. These views are public synonym on V_$ views and are created at time instance is created. Thus, dynamic performance views (or) V$ views are available from instance startup even at NO MOUNT stage and as long as the database is available for use. Data dictionary views are available only after database is created, open and data dictionary tables become accessible.
• Data dictionary views are of three types – DBA_X – these views are accessible only by SYS user and used to get information on all objects in a database;  ALL_X – these views contain information about a particular user, and all objects accessible by that user, USER_ X–  these views contain information accessible to that user alone.
• Dynamic performance view occur in only one form (V$) which is accessible by everyone.
• Data dictionary tables are owned by SYS user. Dynamic performance views collect information from instance memory, control file and hence accessible to everyone.
• DBA_OBJECTS,DBA_TABLESPACES, DBA_ALERT_HISTORY, DBA_USERS, DBA_TABLES are some popular data dictionary views.
• V$tablespace,V$controlfile, v$logfile, v$database are popular dynamic performance views. There is a overlap. DBA_TABLESPACES and V$TABLESPACES present the same set of information.
• Data dictionary views manages the database while Dynamic performance views (V$ views) manages the instance.

Post your answers to this Assignment to the class page

Question: List the names of the views which:

1. Gives the names of the tables (along with the name of the tablespace where they reside) that have been created in your schema?
2. Gives significant information about the sequences in the database that you have access to?
3. Tells you the users currently logged on to the database.
4. Gives you  information about all the indexes in the database.

Next

Whenever a critical error occurs, two file types will be created:

1. Trace files

2. Dump files

Trace files : Trace files have periodic, line-by-line information written by a process as it goes along doing its normal operation.

Dump files : Dumps are typically large blocks of information written in response to an error or other exceptional condition.

For each incident reported, there will be a trace file and incident dumps.

Process trace files are kept in the ADR HOME/trace or $ORACLE_BASE/diag/rdbms/ORACLE_SID/ORACLE_SID/trace directory and will have a name <SID>_ora_<PID>.trc.

When there is a critical error in the database, an incident and a corresponding incident directory will be created.
The incident dump file will appear in the incident directory.

The alert log will show the trace file and incident dump for the error reported as shown below.

kudtst_dbrm_23068.trc is the trace file.

kudtst_dbrm_23068_i7793.trc is the incident dump.

In most of the cases , incident dump will have the errorstack and will have more information for the error reported .

To identify the trace file from ADRCI :

1. run adrci
2. Find out the incident number.
3. Find out the trace file.

1) Find out incident number.

2. Get the trace file for incident_id 7793

This will open the incident dump file. The incident dump file will also contain a pointer to the process trace file as shown below.

Next

• Review the contents of a tablespace
• Assigning tablespace quotas
• Locally vs Dictionary managed tablespaces
• Types of tablespaces
• Temp tablespace groups
• Creating, renaming, dropping tablespaces
• Viewing information in tablespaces

Next

Oracle databases are made up of  physical and logical structures.

• Physical structures can be seen and managed at the operating system level (on your Linux machine). This means that you can see these files when you log on to your Linux machine and go to the directory where they are stored.
• Logical structures are created and recognized by Oracle Database and are not known to the operating system. Examples are  tablespaces, tables, contains physical files. The applications developer or user may be aware of the logical structure, but is not usually aware of this physical structure.You MUST must understand the relationship between the physical and logical structures  in a database.

Control Files

A control file tracks the physical components of the database. It is the root file that the database uses to find all the other files used by the database. Because of the importance of the control file, Oracle recommends that the control file be multiplexed, or have multiple identical copies. For databases created with Oracle Database Configuration Assistant (DBCA), two copies of the control file are automatically created and kept synchronized with each other.

If any control file fails, then your database becomes unavailable. If you have a control file copy, however, you can shut down your database and re-create the failed control file from the copy, then restart your database. Another option is to delete the failed control file from the CONTROL_FILES initialization parameter and restart your database using the remaining control files.

Note that control files are binary files..this means that you cannot open them using a text editor such as VI. If you try to open them, it will get corrupted. You can create a text-based version of your control file using the alter database backup controlfile to trace;statement. When this is issued, a text-base copy of the control file is copied to the trace directory where you can go and copy to a temp directory and edit/review.

Example of the kuddev control file we just created by issuing the command above

Online Redo Log Files
Oracle databases consist of two or more online redo log files. The set of online redo log files is collectively known as the redo log for the database. A redo log is made up of redo entries, which are also called redo records.

The online redo log stores a copy of the changes made to data. If a failure requires a data file to be restored from backup, then the recent data changes that are missing from the restored data file can be obtained from the online redo log files, so work is never lost.

The online redo log files are used to recover a database after hardware, software, or media failure. To protect against a failure involving the online redo log file itself, Oracle Database can multiplex the online redo log file so that two or more identical copies of the online redo log file can be maintained on different disks.

The online redo log for a database consists of groups of online redo log files. A group consists of an online redo log file and its multiplexed copies. Each identical copy is considered to be a member of that group. Each group is defined by a number, such as Group 1.

The database log writer process (LGWR) writes redo records from the memory buffer to a redo log group until the log files in that group reach their storage size limit, or until you request a log switch operation. The LGWR process then writes to the next log group. The LGWR process performs this action in a circular fashion so that the oldest group is overwritten by the most recent redo records.

Archived Redo Log Files
When you configure your database in archive log mode, the online redo logs are copied to a separate directory mounted on the file system before they are over written. This process is know as archiving. You can archive the to multiple locations and eventually to tape backup for long term storage.

These archived redo log files extend the amount of redo data that can be saved and are used for recovery. Archived redo log files are used to recover a database from a previous backup of the database from the time of the backup to the current time.

Most production databases run in archive log mode. Oracle recommends that the archived redo logs be stored in the fast recovery area (this is just an area of storage on disk configured for backup and recovery purposes).

 Undo Segments

Undo segments are  used to store and manage undo information for the database.

Oracle Database uses a SYSTEM undo segment for performing system transactions. It is created automatically when the database is created, and is always brought online at instance startup. It is located in the SYSTEM tablespace. You are not required to perform any operations to manage the SYSTEM rollback segment.

Data Files

Data files are the operating system files that store the data within the database. The data is written to these files in an Oracle proprietary format that cannot be read by other programs. Tempfiles are a special class of data files that are associated only with temporary tablespaces.

Example of a datafile called the system datafile for the kuddev database.

Example of temp datafile for the kuddev database

Data files are made up of the  following components:

• Segment – Is made up of extents. Contains a specific type of database object. For example, a table is stored in a table segment, and an index is stored in an index segment. A data file can contain many segments.

• Extent – is a contiguous set of data blocks within a segment. Oracle Database allocates space for segments in units of one extent. When the existing extents of a segment are full, the database allocates another extent for that segment.

• Data block – A data block, also called a database block, is the smallest unit of I/O to database storage. An extent consists of several contiguous data blocks. The database uses a default block size at database creation.

After the database has been created, it is not possible to change the default block size without re-creating the database. It is possible, however, to create a tablespace with a block size different than the default block size.

Note that segments, extents, and data blocks are all logical structures. Only Oracle Database can determine how many data blocks are in a file. The operating system recognizes only files and operating system blocks, not the number of data blocks in an Oracle Database file. Each data block maps to one or more operating system blocks.

Next

How Table Data Is Stored

When a table is created, a segment is created to hold its data. A tablespace contains a collection of segments.

Logically, a table contains rows of column values. A row is ultimately stored in a database block in the form of a row piece. It is called a row piece because, under some circumstances, the entire row may not be stored in one place. This happens when an inserted row is too large to fit into a single block (chained row) or when an update causes an existing row to outgrow the available free space of the current block (migrated row). Row pieces are also used when a table has more than 255 columns. In this case the pieces may be in the same block (intra-block chaining) or across multiple blocks.

Database Block: Contents

• Block header: The block header contains the segment type (such as table or index), data block address, table directory, row directory, and transaction slots of approximately 23 bytes each, which are used when modifications are made to rows in the block. The block header grows downward from the top.
• Row data: This is the actual data for the rows in the block. Row data space grows upward from the bottom.
• Free space: Free space is in the middle of the block, enabling the header and the row data space to grow when necessary. Row data takes up free space as new rows are inserted or as columns of existing rows are updated with larger values.

Examples of events that cause header growth:

–    Row directories that need more row entries

–    More transaction slots required than initially configured

Initially, the free space in a block is contiguous. However, deletions and updates may fragment the free space in the block. The free space in the block is coalesced by the Oracle server when necessary.

How To Create a Tablespace

A tablespace must have at least one file. Choose the appropriate storage type, depending on your environment. Bigfile tablespaces are used with extremely large databases, in which ASM or other logical volume managers support the striping or redundant array of independent disks (RAID) and dynamically extensible logical volumes.

You can create additional tablespaces to store user data, so that not all data is stored in the USERS tablespace. The following are some reasons to create additional tablespaces:

• For certain users, groups of users, or applications, it may be convenient to keep all application data in a separate tablespace or set of tablespaces for backup and recovery or maintenance reasons. For example, suppose you must recover all application data from backup due to a hardware or software failure, and you want to perform an offline recovery. If the application data is kept in a separate tablespace, then you can take just that tablespace offline and recover it, without affecting the operation of other database applications.
• Some applications, such as those with large partitioned tables, may benefit from distributing data across multiple tablespaces. This approach allows the optimal use of the available storage because frequently accessed data can be placed on high performance disks, and infrequently retrieved data can be placed on less expensive storage.

To check that the tablespace has been created, query the dba_tablespace view.

Storage for Tablespaces

Extent Allocation: The extents in a locally managed tablespace can be allocated in one of these two ways:

• Automatic: Also called autoallocate, it specifies that the sizes of the extents in the tablespace are system managed. You cannot specify Automatic for a temporary tablespace.
• Uniform: It specifies that the tablespace is managed with uniform extents of a size that you specify. The default size is 1 MB. All extents of temporary tablespaces are uniform. You cannot specify Uniform for an undo tablespace.

Segment Space Management: Segment space management in a locally managed tablespace can be specified as:

• Automatic: The Oracle database uses bitmaps to manage the free space in segments. The bitmap describes the status of each data block in a segment with respect to the amount of space in the block that is available for inserting rows. As more or less space becomes available in a data block, the new state is reflected in the bitmap. With bitmaps, the Oracle database manages free space more automatically. As a result, this form of space management is called Automatic Segment Space Management (ASSM).
• Manual: This specifies that you want to use free lists for managing free space in segments. Free lists are lists of data blocks that have space available for inserting rows. This form of managing space in segments is called manual segment spacemanagement because of the need to specify and tune the PCTUSED, FREELISTS, and FREELISTGROUPSstorage parameters for schema objects created in the tablespace. This is supported for backward compatibility; it is recommended that you use ASSM.

Compression Options: Data segment compression is disabled by default. Enabling data segment compression can save disk space usage, reduce memory use in the buffer cache, and speed up query execution during reads. There is, however, a cost in CPU overhead for data loading and DML. It is especially useful in online analytical processing (OLAP) systems, where there are lengthy read-only operations, but can also be used in online transaction processing (OLTP) systems.

Logging: The logging clause sets the default logging value for any segment created in the tablespace. Changes made to objects in the tablespace are written to the redo log. If logging is not enabled, any direct loads using SQL*Loader and direct load INSERT operations are not written to the redo log, and the objects are thus unrecoverable in the event of data loss. When an object is created without logging enabled, you must back up those objects if you want them to be recoverable. Choosing not to enable logging can have a significant impact on the ability to recover objects in the future. Use with caution.

Note: If FORCE LOGGING mode is in effect for the database, it takes precedence over the tablespace logging setting. The database can be put into FORCE LOGGING mode at the time of database creation or after database creation using the alter database force logging command.

 Important Tablespaces in the Database

The following tablespaces are created in the preconfigured database in this course:

• SYSTEM: The SYSTEM tablespace is used by the Oracle server to manage the database. It contains the data dictionary and tables that contain administrative information about thedatabase. These are all contained in the SYS schema and can be accessed only by the SYS user or other administrative users with the required privilege.

• SYSAUX: This is an auxiliary tablespace to the SYSTEM tablespace. Some components and products that used the SYSTEM tablespace or their own tablespaces in earlier releases of Oracle Database now use the SYSAUX tablespace. Every Oracle Database 10g (or later release) database must have a SYSAUX tablespace. After creation, you can monitor the space usage of each occupant in the SYSAUX tablespace. If you detect that a component is taking too much space in the SYSAUX tablespace, or if you anticipate that it will, you can move the occupant into a different tablespace.

• TEMP: Your temporary tablespace is used when you execute a SQL statement that requires the creation of temporary segments (such as a large sort or the creation of an index). Just as each user is assigned a default tablespace for storing created data objects, each user is assigned a temporary tablespace. The best practice is to define a default temporary tablespace for the database, which is assigned to all newly created users unless otherwise specified.
• UNDOTBS1: This is the undo tablespace used by the database server to store undo information. If a database uses Automatic Undo Management, then it can only use a single undo tablespace at any given time. This tablespace is created at database creation time.
• USERS: This tablespace is used to store user objects and data. If no default tablespace is specified when a user is created then the USERS tablespace is the default tablespace for all objects created by that user.For the SYS and SYSTEM users, the default permanent tablespace is SYSTEM.

Note: To simplify administration, it is common to have a tablespace for indexes alone.

Altering a Tablespace

After you create a tablespace, you can alter it in several ways as the needs of your system change.

Renaming: You can rename a tablespace as shown below.

Changing the status: A tablespace can be in one of three different statuses or states. Any of the following three states may not be available because their availability depends on the type of tablespace.

• Read Write: The tablespace is online and can be read from and written to.
  SQL> ALTER TABLESPACE USER_DATA4 READ WRITE;
  Tablespace altered.
• Read Only: Specify read-only to place the tablespace in transition read-only mode. In this state, existing transactions can be completed (committed or rolled back), but no further data manipulation language (DML) operations are allowed on objects in the tablespace. The tablespace is online while in the read-only state. You cannot make the SYSTEM or SYSAUX tablespaces read-only.
  SQL> ALTER TABLESPACE USER_DATA4 READ ONLY;
  Tablespace altered.

Note: The undo and temporary tablespaces cannot be made read-only.

Altering a Tablespace

• Offline: You can take an online tablespace offline so that this portion of the database is temporarily unavailable for general use.
  SQL> ALTER TABLESPACE USER_DATA4 OFFLINE;
  Tablespace altered.
  SQL> ALTER TABLESPACE USER_DATA4 ONLINE;
  Tablespace altered.

  You might change the status of a tablespace to Offline before performing maintenance or recovery on the data files associated with that tablespace.
  The rest of the database is open and available for users to access data. When you take it offline, you can use the following options:

–    Normal: A tablespace can be taken offline normally if no error conditions exist for any of the data files of the tablespace. Oracle Database ensures that all data is written to disk by taking a checkpoint for all data files of the tablespace as it takes them offline.

–    Temporary: A tablespace can be taken offline temporarily even if there are error conditions for one or more files of the tablespace. Oracle Database takes the data files (which are not already offline) offline, performing checkpointing on them as it does so. If no files are offline, but you use the Temporary clause, media recovery is not required to bring the tablespace back online. However, if one or more files of the tablespace are offline because of write errors, and you take the tablespace offline temporarily, the tablespace requires recovery before you can bring it back online.

–    Immediate: A tablespace can be taken offline immediately without Oracle Database taking a checkpoint on any of the data files. When you specify Immediate, media recovery for the tablespace is required before the tablespace can be brought online. You cannot take a tablespace offline immediately if the database is running in NOARCHIVELOG mode.

Note: System tablespaces may not be taken offline.

You may want to take a tablespace offline for any of the following reasons:
• To make a portion of the database unavailable while still allowing access to the remainder of the database
• To make an application and its group of tables temporarily unavailable while updating or maintaining the application
• To perform an offline tablespace backup (even though a tablespace can be backed up while online and in use)
• To recover a tablespace after a hardware or software failure
• To rename or relocate tablespace data files

Changing the size: You can add space to an existing tablespace by either adding data files to the tablespace or changing the size of an existing data file.
As a segment grows in size, Oracle allocates extents to it from the free space in its datafiles.

• When the tablespace starts to fill up, you can either add new datafiles to it or extend the size of the existing datafiles by using the RESIZE command.

• To add a new data file to the tablespace.
  • You can manually resize a datafiles. Initially, the file was 500MB, and the following command reduces the size of the file to 250MB. Note that you need to use the ALTER DATABASE command, not the ALTER TABLESPACE command, to resize a datafile.

• • You can use the AUTO EXTEND option when you create a tablespace or when you add datafiles to a tablespace to tell Oracle to automatically extend the size of the datafiles in the tablespace to a specified maximum. Here’s the syntax for using the AUTO EXTEND feature:
    SQL>
    ALTER TABLESPACE KUDDEV_TS ADD DATAFILE ‘/opt/u06/oradata/kuddevt03.dbf’ SIZE 200M AUTO EXTEND ON NEXT 10M MAXSIZE 1000M;

Note: You cannot add additional data files to bigfile tablespaces.

You can change the size of the data file. You can make the tablespace either larger or smaller. However, you cannot make a data file smaller than the used space in the file; if you try to do so, you get the following error:

ORA-03297: file contains used data beyond requested RESIZE value

Actions with Tablespaces Review

You can perform the following tasks  with your tablespaces.

• Add Datafile: Adds a data file to the tablespace, which makes the tablespace larger
• Create Like: Creates a tablespace .
• Generate DDL: Generates the data definition language (DDL) statement that creates the tablespace. This can then be copied and pasted into a text file for use as a script or for documentation purposes.
• Make Locally Managed: Converts the tablespace to locally managed if the tablespace is currently dictionary managed. This conversion is one-way only; you cannot convert the tablespace back to dictionary managed. You can use the PL/SQL package DBMS_SPACE_ADMIN. TABLESPACE_MIGRATE_FROM_LOCAL to convert to dictionary managed if needed.
• Make Readonly: Stops all writes to the tablespace. Current transactions are allowed to complete, but no new DML or other write activities are allowed to start on the tablespace. This appears only if the tablespace is currently not read-only.
• Make Writable: Allows DML and other write activities to be initiated on objects in the tablespace. This appears only if the tablespace is currently not writable.
• Place Online: Brings a currently offline tablespace online

• Reorganize: Allows you to move objects around in the tablespace to reclaim space that otherwise may not be used. This is a task that should be performed during off-peak usage of the objects in the tablespace.
• Show Dependencies: Shows objects on which this tablespace depends, or objects that depend on this tablespace
• Show Tablespace Contents: Shows information about all the segments in the tablespace, including a graphical map of all of the extents
• Take Offline: Makes a currently online tablespace unavailable. The tablespace is not deleted or dropped; it is just unavailable.

Dropping Tablespaces

You can drop a tablespace and its contents (the segments contained in the tablespace) from the database if the tablespace and its contents are no longer required. You must have the DROP TABLESPACE system privilege to drop a tablespace.

When you drop a tablespace, the file pointers in the control file of the associated database are removed. If you are using Oracle-managed files (OMF), the underlying operating system files are also removed. Otherwise, without OMF, you can optionally direct the Oracle server to delete the operating system files (data files) that constitute the dropped tablespace. If you do not direct the Oracle server to delete the data files at the same time that it deletes the tablespace, you must later use the appropriate commands of your operating system if you want them to be deleted.

You cannot drop a tablespace that contains active segments. For example, if a table in the tablespace is currently being used or if the tablespace contains undo data that is needed to roll back uncommitted transactions, you cannot drop the tablespace. The tablespace can be online or offline, but it is best to take the tablespace offline before dropping it.

ALWAYS BACKUP YOUR DATABASE BEFORE DROPPING A TABLESPACE OR MAKING ANY STRUCTURAL CHANGES.

Viewing Tablespace Information

You can query the following view to see the contents of tablespaces

–     DBA_TABLESPACES
–     V$TABLESPACE

• Data file information:

–   DBA_DATA_FILES
– V$DATAFILE

Note: The V$DBFILE view displays all data files in the database. This view is retained for historical compatibility. Use of V$DATAFILEis recommended instead.

• Temp file information:

–  DBA_TEMP_FILES
– V$TEMPFILE

Locally Managed Tablespaces
Space management within a tablespace involves keeping track of available (free) and used space, so that space is allocated efficiently during data insertion and deletion.
Locally managed tablespaces keep the space allocation information within the tablespace, not in the data dictionary, thus offering better performance. By default, Oracle Database sets all newly created tablespaces to be locally managed with automatic segment management, a feature that further improves performance.

There are three types of tablespaces:
• Permanent
You use permanent tablespaces to store your user and application data. Oracle Database uses permanent tablespaces to store permanent data, such as system data. Each user is assigned a default permanent tablespace.
• Undo
A database running in automatic undo management mode transparently creates and manages undo data in the undo tablespace. Oracle Database uses undo data to roll back transactions, to provide read consistency, to help with database recovery, and to enable features such as Oracle Flashback Query. A database instance can have only one active undo tablespace.
• Temporary
Temporary tablespaces are used for storing temporary data, as would be created when SQL statements perform sort operations. An Oracle database gets a temporary tablespace when the database is created.

You would create another temporary tablespace if you were creating a temporary tablespace group. Under typical circumstances, you do not have to create additional temporary tablespaces. If you have an extremely large database, then you might configure additional temporary tablespaces.

  Autoextend Tablespace

You can set a tablespace to automatically extend itself by a specified amount when it reaches its size limit. If you do not enable autoextend, then you are alerted when the tablespace reaches its critical or warning threshold size. The critical and warning threshold parameters have default values that you can change at any time.

These parameters also cause alerts to be generated for auto extending tablespaces that are approaching their specified size limit. You can respond to size alerts by manually increasing the tablespace size. You do so by increasing the size of one or more of the tablespace data files or by adding another data file to the tablespace.

Encrypted Tablespaces

Encrypted tablespaces primarily protect your data from unauthorized access by means other than through the database. For example, when encrypted tablespaces are written to backup media for travel from one Oracle database to another or for travel to an offsite facility for storage, they remain encrypted. Also, encrypted tablespaces protect
data from users who try to circumvent the security features of the database and access database files directly through the operating system file system.

You can encrypt any permanent tablespace to protect sensitive data. When you encrypt a tablespace, all tablespace blocks are encrypted. All segment types are supported for encryption, including tables, clusters, indexes, LOBs, table and index partitions, and so on. Tablespace encryption is completely transparent to your applications, so no application modification is necessary.

Other Storage Structures
Other storage structures that can exist in an Oracle database include the initialization parameter file, the password file, and backup files.

Initialization Parameter File
Initialization parameters are used by the Oracle instance at startup to determine the run-time properties and resources for the database. Some parameters can be set or modified while the database is running. Other initialization parameters require the database to be restarted for the changes to take effect.

Password File
A database can use a password file to authenticate administrative users with SYSDBA, SYSOPER, and SYSBACKUP privileges. A password file is required for remote connections to the database with any of these privileges. These privileges enable a DBA to start and shut down the database, back up and recover the database, and perform other high-level administrative tasks. This password file is outside of the database itself, thereby enabling the authentication of a DBA when the database is not yet started. (A DBA must authenticate before starting the database.)

When you invoke DBCA as part of the Oracle Database installation process, DBCA creates a password file with one entry: the SYS user. Granting SYSDBA, SYSOPER, or SYSBACKUP to a user adds that user to the password file automatically.

Backup Files
Backup files are not technically database files, but are copies of the database in some form that can be used to recover the database if a failure causes loss of data.

Viewing Datafile Information

You can use view the location and status information about data files.

Viewing Control File Information
You can use view the location and status information about control files.

Viewing Online Redo Log File Information

Viewing Archived Redo Log File Information

Managing the Online Redo Log
The online redo log files are a critical component in database recovery. Every transaction in the database updates the redo logs, regardless of whether archiving is enabled. During crash, instance, or media recovery, the database properly applies redo log files in ascending order by using the log sequence number of the necessary archived and redo log files. If properly configured, the online redo logs require little maintenance.

Switching a Log File
When a log switch occurs, the log writer (LGWR) process stops writing to the current online redo log group and starts writing to the next available redo log group. After a log switch, the current online redo log group becomes inactive, and the next available online redo log group becomes the current online redo log group. You can switch a log file.

You can force a log switch to make the current redo group inactive and available for redo log maintenance operations. Forcing a log switch is useful in configurations with large redo log files that take a long time to fill. For example, you might want to:

• Drop the current redo group, but are not able to do so until the group is inactive
• Archive the current online redo log group members immediately, even though they are not yet completely filled To switch a log file:
  Alter system switch logfile;

Managing Undo Data
Beginning with Oracle Database 11g, for a default installation, Oracle Database automatically manages the undo data. There is typically no need for database administrator (DBA) intervention. However, if your installation uses Oracle Flashback operations, then you may have to perform some undo management tasks to ensure the success of these operations.

About Undo Data
When a transaction modifies data, Oracle Database copies the original data before modifying it. The original copy of the modified data is called undo data. Saving this information is necessary for the following reasons:
• To undo any uncommitted changes made to the database if a rollback is necessary. A rollback can be needed because a user wants to undo the changes of a misguided or unintentional transaction, or it can be part of a recovery operation.

• To provide read consistency, which means that each user can get a consistent view of data, even while other changes may be occurring against the data. With read consistency, a user session does not see uncommitted changes made in other user sessions (sometimes referred to as dirty reads). For example, if a user issues a query
  at 10:00 a.m. and the query lasts for 15 minutes, then the query results reflect the entire state of the data at 10:00 a.m., regardless of update or insert operations performed by other users after the query started.
• To enable certain Oracle Flashback features, such as Oracle Flashback Query and Oracle Flashback Table, which enable you to view or recover data to a previous point in time.

Undo Tablespace
With automatic undo management, undo data is stored in an undo tablespace. Undo tablespaces have additional properties beyond those of permanent tablespaces. There can be multiple undo tablespaces, but only one can be active for an Oracle instance.

When you create the database using Database Configuration Assistant (DBCA), it creates an autoextending undo tablespace named UNDOTBS1, with a maximum extension size of 32,767 MB.

Undo Retention
Oracle Database automatically ensures that undo data that is in use by an active transaction is never overwritten until that transaction has been committed. After the transaction has been committed, the space occupied by that undo data can be reused, or overwritten. In this case, that undo data could be overwritten if space in the undo tablespace becomes scarce.

Even after a transaction has been committed, it is useful to retain (not overwrite) its undo data, to ensure the success of Oracle Flashback features and for read consistency for long-running queries. To this end, the database maintains and automatically tunes an undo retention period. Committed undo data whose age is less than the undo retention period is retained for use by queries or Oracle Flashback operations.

Next

Oracle-managed files simplifies the administration of oracle operating system files.  The database internally uses standard file system interfaces to create and delete files as needed for the following database structures:

• Tablespaces
• Redo log files
• Control files
• Archived logs
• Block change tracking files
• Flashback logs
• RMAN backups

Through initialization parameters, you specify the file system directory to be used for a particular type of file. The database then ensures that a unique file, an Oracle managed file, is created and deleted when no longer needed.

This feature does not affect the creation or naming of administrative files such as trace files, audit files, alert logs, and core files.

Who Can Use Oracle Managed Files?

Oracle Managed Files are most useful for the following types of databases:

• Databases that are supported by the following:
  • A logical volume manager that supports striping/RAID and dynamically extensible logical volumes
  • A file system that provides large, extensible files
• Low end or test databases

The Oracle Managed Files feature is not intended to ease administration of systems that use raw disks. This feature provides better integration with operating system functionality for disk space allocation. Since there is no operating system support for allocation of raw disks (it is done manually), this feature cannot help. However, because Oracle Managed Files require that you use the operating system file system (unlike raw disks), you lose control over how files are laid out on the disks and thus, you lose some I/O tuning ability.

DB_CREATE_ONLINE_LOG_DEST_1 = '/u02/oradata'
DB_CREATE_ONLINE_LOG_DEST_2 = '/u03/oradata'

/o1_mf_%t_%u_.dbf

DB_CREATE_FILE_DEST   = '/08/backup/omf'
DB_UNIQUE_NAME = KUDDEV

/08/backup/omf/kuddev/datafile/o1_mf_tbs1_2ixh90q_.dbf

Next

You should be very familiar with the following views to manage tablespaces-

Tablespace and data file information can also be obtained by querying the following:

• Data file information:–    DBA_TABLESPACES –     V$TABLESPACE
  SQL> DESC V$TABLESPACE
  Name                                                     Null?                 Type
  —————————————– ——– —————————-
  TS#                                                                                  NUMBER
  NAME                                                                             VARCHAR2(30)
  INCLUDED_IN_DATABASE_BACKUP                 VARCHAR2(3)
  BIGFILE                                                                         VARCHAR2(3)
  FLASHBACK_ON                                                         VARCHAR2(3)
  ENCRYPT_IN_BACKUP                                             VARCHAR2(3)
  CON_ID                                                                          NUMBER

  SQL> desc DBA_TABLESPACES
  Name                                      Null?    Type
  —————————————– ——– —————————-
  TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
  BLOCK_SIZE                                NOT NULL NUMBER
  INITIAL_EXTENT                                     NUMBER
  NEXT_EXTENT                                        NUMBER
  MIN_EXTENTS                               NOT NULL NUMBER
  MAX_EXTENTS                                        NUMBER
  MAX_SIZE                                           NUMBER
  PCT_INCREASE                                       NUMBER
  MIN_EXTLEN                                         NUMBER
  STATUS                                             VARCHAR2(9)
  CONTENTS                                           VARCHAR2(21)
  LOGGING                                            VARCHAR2(9)
  FORCE_LOGGING                                      VARCHAR2(3)
  EXTENT_MANAGEMENT                                  VARCHAR2(10)
  ALLOCATION_TYPE                                    VARCHAR2(9)
  PLUGGED_IN                                         VARCHAR2(3)
  SEGMENT_SPACE_MANAGEMENT                           VARCHAR2(6)
  DEF_TAB_COMPRESSION                                VARCHAR2(8)
  RETENTION                                          VARCHAR2(11)
  BIGFILE                                            VARCHAR2(3)
  PREDICATE_EVALUATION                               VARCHAR2(7)
  ENCRYPTED                                          VARCHAR2(3)
  COMPRESS_FOR                                       VARCHAR2(30)
  DEF_INMEMORY                                       VARCHAR2(8)
  DEF_INMEMORY_PRIORITY                              VARCHAR2(8)
  DEF_INMEMORY_DISTRIBUTE                            VARCHAR2(15)
  DEF_INMEMORY_COMPRESSION                           VARCHAR2(17)
  DEF_INMEMORY_DUPLICATE                             VARCHAR2(13)
  SHARED                                             VARCHAR2(13)
  DEF_INDEX_COMPRESSION                              VARCHAR2(8)
  INDEX_COMPRESS_FOR                                 VARCHAR2(13)
  DEF_CELLMEMORY                                     VARCHAR2(14)
  DEF_INMEMORY_SERVICE                               VARCHAR2(12)
  DEF_INMEMORY_SERVICE_NAME                          VARCHAR2(1000)
  LOST_WRITE_PROTECT                                 VARCHAR2(7)
  CHUNK_TABLESPACE                                   VARCHAR2(1)

• Data file information

–    DBA_DATA_FILES – V$DATAFILE

Note: The V$DBFILE view displays all data files in the database. This view is retained for historical compatibility. Use of V$DATAFILE is recommended instead.

• Temp file information:

–    DBA_TEMP_FILES – V$TEMPFILE

Next

You will learn how to:

1. Create and manage user accounts
2. Grant and revoke roles
3. Create and manage roles
4. Create and manage profiles

Next

You will learn how to :

• Create and configure oracle listeners.
• Create oracle net services aliases.
• Configure oracle named services.
• Learn how to use tnsping and ping to test oracle database connectivity.

Next

Testing Oracle Net Connectivity

Tnsping is the Oracle Net equivalent of the TCP/IP ping utility. It offers a quick test to verify that the network path to a destination is good. For example, enter tnsping kuddev in a command-line window.

The utility validates that the host name, port, and protocol reach a listener. It does not actually check whether the listener handles the service name. The tnsping utility also reveals the location of the configuration files. In a system with multiple ORACLE_HOME locations, this can be helpful.

You should become familiar with the following troubleshooting tools on the OS.

Ping – check if your client can connect to another machine on the network
Tnsping –check if the service name is configured properly and can resolve.

1. Ping
   If you are troubleshooting a client connectivity issue and tnsping does not resolve the service name…sometimes you may see a connection timed out error.
   Run the OS ping utility to check if the node is alive
   [69-64-90-149:oracle]$ping 69.64.90.149
   PING 69.64.90.149 (69.64.90.149) 56(84) bytes of data.
   64 bytes from 69.64.90.149: icmp_seq=1 ttl=64 time=0.009 ms
   64 bytes from 69.64.90.149: icmp_seq=2 ttl=64 time=0.026 ms
   64 bytes from 69.64.90.149: icmp_seq=3 ttl=64 time=0.018 ms
   64 bytes from 69.64.90.149: icmp_seq=4 ttl=64 time=0.022 ms

   If the IP address is not resolving, then talk to your network admin.

2. Tnsping
   You can test that the service name resolves by using the tnsping utility. Tnsping service_name

   [69-64-90-149:oracle]$tnsping kudtstTNS Ping Utility for Linux: Version 12.2.0.1.0 – Production on 27-APR-2017 17:20:25Copyright (c) 1997, 2016, Oracle.  All rights reserved.

   Used parameter files:

   /home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora

   Used TNSNAMES adapter to resolve the alias

   Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 69-64-90-149.phx.dedicated.codero.com)(PORT = 1529)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = kudtst.phx.dedicated.codero.com)))

   OK (30 msec)

   Very Important Information Below. ACT ON IT NOW
   Follow the link to read the additional notes in its entirety.

   Oracle Net Services Additional Notes

Next

Oracle Net Services enables network connections from a client or middle-tier application to the Oracle server. After a network session is established, Oracle Net acts as the data courier for both the client application and the database server. It is responsible for establishing and maintaining the connection between the client application and database server, as well as exchanging messages between them. Oracle Net (or something that simulates Oracle Net, such as Java Database Connectivity) is located on each computer that needs to talk to the database server.

On the client computer, Oracle Net is a background component for application connections to the database.

On the database server, Oracle Net includes an active process called the Oracle NetListener, which is responsible for coordinating connections between the database and external applications.

Next

If the CONNECT packet requests a valid service name, the listener spawns a new process to deal with the connection. This new process is known as the server process.

The listener connects to the process and passes the initialization information, including the address information for the user process. At this point, the listener no longer deals with the connection and all work is passed to the server process.

The server process checks the user’s authentication credentials (usually a password), and if the credentials are valid, a user session is created.

Dedicated server process: With the session established, the server process now acts as the user’s agent on the server. The server process is responsible for:

• Parsing and running any SQL statements issued through the application
• Checking the database buffer cache for data blocks required to perform SQL statements
•  Reading necessary data blocks from data files on the disk into the database buffer cache portion of the System Global Area (SGA), if the blocks are not already present in the SGA
• Managing all sorting activity. The Sort Area is a memory area that is used to work with sorting; it is contained in a portion of memory that is associated with the Program Global Area (PGA).
• Returning results to the user process in such a way that the application can process the information
• Reading auditing options and reporting user processes to the audit destinatio

Next

Oracle Net provides support for the following naming methods:

• Easy connect naming: The easy connect naming method enables clients to connect to an Oracle database server by using a TCP/IP connect string consisting of a host name and optional port and service name as follows:

CONNECT username/password@host[:port][/service_name] The easy connect naming method requires no
configuration.

• Local naming: The local naming method stores connect descriptors (identified by their net service name) in a local configuration file named tnsnames.ora on the client.
• Directory naming: To access a database service, the directory naming method stores connect identifiers in a centralized directory server that is compliant with the Lightweight Directory Access Protocol (LDAP).
• External naming: The external naming method stores net service names in a supported non-Oracle naming service. Supported third-party services include Network Information Service (NIS) External Naming and    Distributed Computing Environment (DCE) ,Cell Directory Services (CDS)

Easy Connect

With Easy Connect, you supply all information that is required for the Oracle Net connection as part of the connect string. Easy Connect connection strings take the following form:

<username>/<password>@<hostname>:<listener port>/<service name>

The listener port and service name are optional. If the listener port is not provided, Oracle Net assumes that the default port of 1521 is being used. If the service name is not provided, Oracle Net assumes that the database service name and host name provided in the connect string are identical.

Assuming that the listener uses TCP to listen on port 1521 and the SERVICE_NAMES=kudo1 and DB_DOMAIN=  phx.dedicated.codero.com instance parameters, the connect string shown in the slide can be shortened:

Local Naming

With local naming, the user supplies an alias for the Oracle Net service. Oracle Net checks the alias against a local list of known services and, if it finds a match, converts the alias into host, protocol, port, and service name.

One advantage of local naming is that the database users need to remember only a short alias rather than the long connect string required by Easy Connect.

The local list of known services is stored in the following text configuration file:$ORACLE_HOME/network/admin/tnsnames.ora

This is the default location of the tnsnames.ora file, but the file can be located elsewhere using the TNS_ADMIN environment variable.

Local naming is appropriate for organizations in which Oracle Net service configurations do not change often.

Example of the tnsnames.ora file:

Directory Naming

With directory naming, the user supplies an alias for the Oracle Net service. Oracle Net checks the alias against an external list of known services and, if it finds a match, converts the alias into host, protocol, port, and service name. Like local naming, database users need to remember only a short alias.

One advantage of directory naming is that the service name is available for users to connect with as soon as a new service name is added to the LDAP directory. With local naming, the database administrator (DBA) must first distribute updated tnsnames.ora files containing the changed service name information before users can connect to new or modified services.

Directory naming is appropriate for organizations in which Oracle Net service configurations change frequently.

External Naming Method

The external naming method stores Net Service names in a supported non-Oracle naming service. Supported third-party services include:

• Network Information Service (NIS) External Naming
• Distributed Computing Environment (DCE) Cell Directory Services (CDS)

Configuring Service Aliases

You can configure a local Oracle Net Service alias using the network configuration assistant tool.

Next

The Oracle Net Listener (or simply the listener) is the gateway to the Oracle instance for all remote user connections.

A single listener can service multiple database instances on a server and thousands of client connections.

You can manage the listener using Enterprise Manager  and control the configuration of the actual listener as well as general parameters such as password protection and log file locations.

You can also configure Oracle Net Services by manually editing the configuration files, if necessary, with a standard operating system (OS) text editor such as vi.

Establishing Net Connections

For an application to connect to a service through an Oracle Net Listener, the application must have information about that service, including the address or host where the listener resides, the protocol that the listener accepts, and the port that the listener monitors. After the listener is located, the final piece of information that the application needs is the name of the service to which it wants to connect.

After Oracle Net names resolution is complete, a connection request is passed from the user or middle-tier application (hereafter referred to as the user process) to the listener. The listener receives a CONNECT packet and checks whether that CONNECT packet is requesting a valid Oracle Net service name.

If the service name is not requested (as in the case of a tnsping request), the listener acknowledges the connect request and does nothing else. If an invalid service name is requested, the listener transmits an error code to the user process.

To make a client or middle-tier connection, Oracle Net requires the client to know the:

• Host where the listener is running
• Port that the listener is monitoring
• Protocol that the listener is using
• Name of the service that the listener is handling

Listener Control Utility

The listeners can be controlled with the lsnrctl command-line utility (or from EM)

When an instance starts, a listener process establishes a communication pathway to the Oracle database. The listener is then able to accept database connection requests.

The listener control utility is used to control the listener. It can use it to:

• Start the listener
• Stop the listener
• Check the status of the listener
• Reinitialize the listener from the configuration file parameters
• Dynamically configure many listeners
• Change the listener password

Lsnrctl yntax :

LSNRCTL> command [listener_name]

When you run the  lsnrctl command, it initates the default listener (called LISTENER and comes bundled with the oracle database software) unless a different listener name is specified or the SET CURRENT_LISTENER command is executed. If the listener name is LISTENER, the listener_name argument can be omitted.

Note: Be sure the run the ora.env/.profile file before running the lsnrctl utility

To see all the options available with the lsnrctl command enter help

You can call the lsnrctl utility from the command utility and from within the LSNRCTL prompt.

lsnrctl status command

The command-line syntax is typically used to execute an individual command or scripted commands. If you plan to execute several consecutive lsnrctl commands, the prompt syntax is more efficient.

Note that the listener_name argument is omitted, and the stop command would thus affect the listener named LISTENER. Prompt syntax must be used if your listener is password protected.

Remember that if your listener is named something other than LISTENER, you must either include the listener name with the command or use the SET CURRENT_LISTENERcommand.  Here are two examples of stopping a listener named kud_listener using prompt syntax:

lsnrctl start kud_listener

lsnrctl status kud_listener

Next

For a listener to forward client connections to an instance, the listener must know the name of the instance and where the instance’s ORACLE_HOME is located. The listener can find this information in two ways:

• Dynamic service registration: in Oracle 11g and 12c  instances automatically register with the default listener on database startup. No additional listener configuration is required for the default listener.
• Static service registration: Earlier releases of the Oracle database do not automatically register with the listener and, therefore, require that the listener configuration file contain a list of all database services that the listener will serve. You may still choose to use static service registration with newer releases if:

–    Your listener is not on the default port of 1521, and you do not want to configure your instance to register with a non default port

–    Your application requires static service registration

The SERVICE_NAMES initialization parameter specifies one or more names by which clients can connect to the instance. The instance registers its service names with the listener. When a client requests a service, the listener determines which instances offer the requested service and routes the client to the appropriate instance.

You can specify multiple service names to distinguish among different uses of the same database, as in this example:

SERVICE_NAMES = kuddev.phx.dedicated.codero.com

You can also use service names to identify a single service that is available from two different databases through the use of replication.

If you do not qualify the names in this parameter with a domain, Oracle qualifies them with the value of the DB_DOMAIN parameter. If DB_DOMAIN is not specified, no domain will be applied to the non qualified SERVICE_NAMES values.

While processing a client connection request, the listener tries to match the value of this parameter with the value of the SERVICE_NAME parameter in the client connect descriptor. If the client connect descriptor uses the SID parameter, the listener does not attempt to map the values. The value for this parameter is typically obtained from the combination of the DB_NAME and DB_DOMAIN parameters (DB_NAME.DB_DOMAIN) in the initialization parameter file, but the value can also contain any valid name used by clients to identify the service.

Next

Configuring Communication Between Databases

A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle database system. However, to access non-Oracle systems, you must use Oracle Heterogeneous Services.

• To create a private database link you must have the CREATE DATABASE LINK system privilege.
• To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege.
• You must also have the CREATE SESSION system privilege on the remote Oracle database.

When an application uses a database link to access a remote database, Oracle Database establishes a database session in the remote database on behalf of the local request. The CONNECT TO clause that is used in creating a database link determines how the connection is established on the remote database.

After you create a database link, you can use it to refer to tables and views on the other database.  You can query a table or view on the other database or use any INSERT, UPDATE, DELETE, or LOCK TABLE statement for the table.

Managing Database Links

A database link enables a one-way connection to a remote database from a local database. The link is one-way only. The remote database users can’t use this link to connect to the local database—they must create a separate database link for that.

A database link allows you to gain access to a different database though a remote database user account; you don’t have to be a user in the remote database. Your privileges on that database will be identical to the privileges of the user account you use when creating the database link. Database links are useful when you want to query a table in a distributed database or even insert data from another database’s table into a local table. Database links allow users to access multiple databases as a single logical database.

There are two types of database links:

• Private database link
  A private database link is owned by the user that creates the link.Now, let’s create a private database link on the kudtst database and use it to connect to the kuddev server in the hr schema.A database link mostly use to access other database objects like (tables/synonym/view etc).

  Syntax to create a database link

  create database link [database-link-name]
  connect to [username]
  identified by [password]
  using [tnsname]where
  database-link-name –> name of the database link
  username –> username of the database from where you want to connect
  password –> password of the usernametnsname –> tnsname of the database from where you want to connect
  You can check the tnsnames entries for your databases in the tnsnames.ora file located under $ORACLE_HOME/network/admin directory
   
  For example:
  database 1 == kuddev
  database 2 == kudtst
   
  now you want to access kuddev database for employee tables from kudtst database, you will connect to kudtst database with database link privileged user to create database link you need ‘create database link’ privilege and them access the hr.employees table from the kudtst database.
  SQL> connect system@kudtst
  Enter password:
  Connected.
  SQL> sho user
  USER is “SYSTEM”
  SQL> create database link kuddev
  connect to hr identified by hr
  using ‘kuddev’;  2    3Database link created.

  After the link is created, the SYSTEM user can query the hr.employees table in the remote
  database.

  SQL> select count(*) from hr.employees@kuddev;

  COUNT(*)
  ———-
  107

  You MUST have the CREATE PRIVATE DATABASE LINK privilege to create a private database link.

• Note that the database link’s name is kuddev, and is the same as the remote database’s TNS name alias (Oracle Net Service alias), but it could be anything you want.
• The CONNECT TO . . . IDENTIFIED BY clause means that the user of this database link will use that username and password to enter the remote database. The USING ‘kuddev’ clause simply specifies the TNS name alias for the linked remote database.
• Since this is a private database link, only the SYSTEM user can use it. When the kudtstdba user tries to use this link to a remote database, this is what happens:
  SQL> connect KUDTSTDBA
  Enter password:
  Connected.
  SQL> sho user
  USER is “KUDTSTDBA”
  SQL> select count(*) from hr.employees@kuddev;
  select count(*) from hr.employees@kuddev
  *
  ERROR at line 1:
  ORA-02019: connection description for remote database not found.
• Public database link
  
  You MUST have the CREATE PUBLIC DATABASE LINK privilege to create a public database link.
  create public database link [database-link-name]
  connect to [username]
  identified by [password]
  using [tnsname]

A public database link, unlike a private database link, enables any user or any PL/SQL program unit to access the remote database objects. You just add the PUBLIC keyword to the CREATE DATABASE LINK statement.

Note that you can create a public database link if several users require access to a remote Oracle database from a
local database. Otherwise, create a private database link, which will allow only the owner of the private database
link to access database objects in the remote database.

Once the public kuddve link is created, any user can log into a remote database using that link. As you can see from the example below, kudtstdba of the kudtst database user can use  the public database link to query the hr.employees table in the kuddev database.

Note that kudtstdba user  can access the remote database, even without being a user in the remote database,
because kudtstdba is using a public database link, which enables any user to use the hr user’s username/password
combination to access the remote database.

How to check the database links currently on the server

you query the DESC DBA_DB_LINKS view.

How to drop database links

Next

Become familiar with these commands.

1. Check the status of the listener- lsnrctl status listener_name
   [69-64-90-149:oracle]$lsnrctl status kud_listenerLSNRCTL for Linux: Version 12.2.0.1.0 – Production on 26-MAY-2017 08:47:15Copyright (c) 1991, 2016, Oracle.  All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=69-64-90-149.phx.dedicated.codero.com)(PORT=1529)))
   STATUS of the LISTENER
   ————————
   Alias                     kud_listener
   Version                   TNSLSNR for Linux: Version 12.2.0.1.0 – Production
   Start Date                06-MAY-2017 16:34:01
   Uptime                    19 days 16 hr. 13 min. 14 sec
   Trace Level               off
   Security                  ON: Local OS Authentication
   SNMP                      OFF
   Listener Parameter File   /home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
   Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/69-64-90-149/kud_listener/alert/log.xml
   Listening Endpoints Summary…
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=69-64-90-149.phx.dedicated.codero.com)(PORT=1529)))
   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1529)))
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=69-64-90-149.phx.dedicated.codero.com)(PORT=5500))(Security=(my_wallet_directory=/home/oracle/app/oracle/admin/kuddev/xdb_wallet))(Presentation=HTTP)(Session=RAW))
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=69-64-90-149.phx.dedicated.codero.com)(PORT=5580))(Security=(my_wallet_directory=/home/oracle/app/oracle/admin/kudtst/xdb_wallet))(Presentation=HTTP)(Session=RAW))
   Services Summary…
   Service “kuddev.phx.dedicated.codero.com” has 1 instance(s).
   Instance “kuddev”, status READY, has 1 handler(s) for this service…
   Service “kuddevXDB.phx.dedicated.codero.com” has 1 instance(s).
   Instance “kuddev”, status READY, has 1 handler(s) for this service…
   Service “kudtst.phx.dedicated.codero.com” has 1 instance(s).
   Instance “kudtst”, status READY, has 1 handler(s) for this service…
   Service “kudtstXDB.phx.dedicated.codero.com” has 1 instance(s).
   Instance “kudtst”, status READY, has 1 handler(s) for this service…
   The command completed successfully

    

2. lsnrctl stop listener_name – to stop the listener (DO NOT  stop the listener on your database servers unless it is approved because this is considered a database outage).
   [69-64-90-149:oracle]$lsnrctl stop kud_listenerLSNRCTL for Linux: Version 12.2.0.1.0 – Production on 26-MAY-2017 08:50:52Copyright (c) 1991, 2016, Oracle.  All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=69-64-90-149.phx.dedicated.codero.com)(PORT=1529)))
   The command completed successfully
3. lsnrctl start listener_name – to start the listener.
   [69-64-90-149:oracle]$lsnrctl start kud_listener
   LSNRCTL for Linux: Version 12.2.0.1.0 – Production on 26-MAY-2017 08:51:33Copyright (c) 1991, 2016, Oracle.  All rights reserved.Starting /home/oracle/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr: please wait…TNSLSNR for Linux: Version 12.2.0.1.0 – Production
   System parameter file is /home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
   Log messages written to /home/oracle/app/oracle/diag/tnslsnr/69-64-90-149/kud_listener/alert/log.xml
   Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=69-64-90-149.phx.dedicated.codero.com)(PORT=1529)))
   Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1529)))Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=69-64-90-149.phx.dedicated.codero.com)(PORT=1529)))
   STATUS of the LISTENER
   ————————
   Alias                     kud_listener
   Version                   TNSLSNR for Linux: Version 12.2.0.1.0 – Production
   Start Date                26-MAY-2017 08:51:33
   Uptime                    0 days 0 hr. 0 min. 0 sec
   Trace Level               off
   Security                  ON: Local OS Authentication
   SNMP                      OFF
   Listener Parameter File   /home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
   Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/69-64-90-149/kud_listener/alert/log.xml
   Listening Endpoints Summary…
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=69-64-90-149.phx.dedicated.codero.com)(PORT=1529)))
   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1529)))
   The listener supports no services
   The command completed successfully
4. tnsping servive_name
   
   [69-64-90-149:oracle]$tnsping kuddev
   TNS Ping Utility for Linux: Version 12.2.0.1.0 – Production on 26-MAY-2017 08:52:22Copyright (c) 1997, 2016, Oracle.  All rights reserved.Used parameter files:
   /home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the alias
   Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 69-64-90-149.phx.dedicated.codero.com)(PORT = 1529)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = kuddev.phx.dedicated.codero.com)))
   OK (0 msec)
5. ping server_name/IP address – to check if the server is online and your machine can talk to it.
   [69-64-90-149:oracle]$ping 69.64.90.149
   PING 69.64.90.149 (69.64.90.149) 56(84) bytes of data.
   64 bytes from 69.64.90.149: icmp_seq=1 ttl=64 time=0.009 ms
   64 bytes from 69.64.90.149: icmp_seq=2 ttl=64 time=0.026 ms
   64 bytes from 69.64.90.149: icmp_seq=3 ttl=64 time=0.018 ms
   64 bytes from 69.64.90.149: icmp_seq=4 ttl=64 time=0.022 ms
6. Check the status of services for which a listener is listening can be checked with the listener SERVICES command.
   [69-64-90-149:oracle]$lsnrctl services KUD_LISTENERLSNRCTL for Linux: Version 12.2.0.1.0 – Production on 27-MAY-2017 13:14:42Copyright (c) 1991, 2016, Oracle.  All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=69-64-90-149.phx.dedicated.codero.com)(PORT=1529)))
   Services Summary…
   Service “kuddev.phx.dedicated.codero.com” has 1 instance(s).
   Instance “kuddev”, status READY, has 1 handler(s) for this service…
   Handler(s):
   “DEDICATED” established:0 refused:0 state:ready
   LOCAL SERVER
   Service “kuddevXDB.phx.dedicated.codero.com” has 1 instance(s).
   Instance “kuddev”, status READY, has 1 handler(s) for this service…
   Handler(s):
   “D000” established:0 refused:0 current:0 max:1022 state:ready
   DISPATCHER <machine: 69-64-90-149.phx.dedicated.codero.com, pid: 20280>
   (ADDRESS=(PROTOCOL=tcp)(HOST=69-64-90-149.phx.dedicated.codero.com)(PORT=45246))
   Service “kudtst.phx.dedicated.codero.com” has 1 instance(s).
   Instance “kudtst”, status READY, has 1 handler(s) for this service…
   Handler(s):
   “DEDICATED” established:0 refused:0 state:ready
   LOCAL SERVER
   Service “kudtstXDB.phx.dedicated.codero.com” has 1 instance(s).
   Instance “kudtst”, status READY, has 1 handler(s) for this service…
   Handler(s):
   “D000” established:0 refused:0 current:0 max:1022 state:ready
   DISPATCHER <machine: 69-64-90-149.phx.dedicated.codero.com, pid: 31096>
   (ADDRESS=(PROTOCOL=tcp)(HOST=69-64-90-149.phx.dedicated.codero.com)(PORT=42944))
   The command completed successfully
7. Check the options that you can use with the LSNRCTL command.
   LSNRCTL> help
   The following operations are available
   An asterisk (*) denotes a modifier or extended command:start           stop            status          services
   servacls        version         reload          save_config
   trace           spawn           quit            exit
   set*            show*
8. To exit out of the LSNRCTL utility, use QUIT or EXIT option.
   LSNRCTL> quit
   [69-64-90-149:oracle]$
   LSNRCTL> exit
   [69-64-90-149:oracle]$
9. Check the version of the listener.
   [69-64-90-149:oracle]$lsnrctl version KUD_LISTENERLSNRCTL for Linux: Version 12.2.0.1.0 – Production on 27-MAY-2017 13:30:57Copyright (c) 1991, 2016, Oracle.  All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=69-64-90-149.phx.dedicated.codero.com)(PORT=1529)))
   TNSLSNR for Linux: Version 12.2.0.1.0 – Production
   TNS for Linux: Version 12.2.0.1.0 – Production
   Unix Domain Socket IPC NT Protocol Adaptor for Linux: Version 12.2.0.1.0 – Production
   Oracle Bequeath NT Protocol Adapter for Linux: Version 12.2.0.1.0 – Production
   sdp
   The command completed successfully
10. To see parameters you can use with the SHOW option from LSNRCTL.
    LSNRCTL> set
    The following operations are available after set
    An asterisk (*) denotes a modifier or extended command:password                             rawmode
    displaymode                          trc_file
    trc_directory                        trc_level
    log_file                             log_directory
    log_status                           current_listener
    inbound_connect_timeout              startup_waittime
    save_config_on_stop                  dynamic_registration
    enable_global_dynamic_endpoint       connection_rate_limit
    valid_node_checking_registration     registration_invited_nodes
    registration_excluded_nodes
11. To see parameters you can use with the SHOW option from LSNRCTL.
    LSNRCTL> show
    The following operations are available after show
    An asterisk (*) denotes a modifier or extended command:rawmode                              displaymode
    rules                                trc_file
    trc_directory                        trc_level
    log_file                             log_directory
    log_status                           current_listener
    inbound_connect_timeout              startup_waittime
    snmp_visible                         save_config_on_stop
    dynamic_registration                 enable_global_dynamic_endpoint
    oracle_home                          pid
    connection_rate_limit                valid_node_checking_registration
    registration_invited_nodes           registration_excluded_nodes
12. To reload the listener. This causes a reread of the listener.ora file. Enables the addition or modification of statically configured services without stopping the listener.

    [69-64-69-66:oracle]$lsnrctl reload KUD_LISTENERLSNRCTL for Linux: Version 11.2.0.4.0 – Production on 27-MAY-2017 13:46:10Copyright (c) 1991, 2013, Oracle.  All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=69-64-69-66.phx.dedicated.codero.com)(PORT=1521)))
    The command completed successfully

Next