Oracle 12c Release 2 Database Creation

By // No comments:


This article explains the step by step procedure to create an Oracle 12c Release 2 (12cR2) database by using Database Configuration Assistant (DBCA).


Oracle 12cR2 Database Creation Using DBCA

1. Create the required directories and set the user permissions.
# mkdir -p /u01/test/oradata
# chown -R oracle:dba /u01/test
# chmod -R 775 /u01/test
2. Login as oracle database administrative user and run dbca from the $ORACLE_HOME/bin location as below.

Select Database Operation

3. Choose Create a database option from the below listed options as we are going to create a new database. Click on Next button.

Select Database Creation Mode

4. Select Advance Configuration option and click on Next button. If you want default configurations for your database, select Typical configuration and click on Next button.

Select Database Deployment Type

5. Select Database Type as Oracle Single instance database from the drop-down menu and select database template as General Purpose or Transaction Processing. Now Click on Next button.

Specify Database Identification Details

6. Provide the details for the Global database name, SID and click on Next button. If you wish to create a container and pluggable databases, Check the mark for Create as Container database and enter the required fields. Otherwise you can skip and go with next step.

Select Database Storage Options

7. Choose Database files storage type from the drop-down menu and specify the path for Database files location. Click on Next button.

Select Fast Recovery Options

8. If you want to configure recovery options for the database, enter the required fields for Specify Fast Recovery Area and Enable archiving. Otherwise and skip this step and Click on Next button.

Specify Network Configuration Details

9. Now you will be presented with a network configuration window. Here you can optionally create a listener by entering the required fields. Click on Next button once you are done.

Select Oracle Data Vault Configuration Options

10. Here, you can optionally configure Oracle Database Vault and Label Security for the database by entering the requested fields. After making the changes as per your requirement and click on Next button.

Specify Database Configuration Options

11. Here, in Oracle12c Release 2 we have three options for memory configuration.
Memory:
Automatic Shared Memory Management:
The automatic shared memory management, will give you the direct control over the sizes of the System Global Area (SGA) and instance Program Global Area (PGA). This will automatically readjust the sizes of the main pools (db_cache_size, shared_pool_size, large_pool_size, java_pool_size) based on existing workloads.
Manual Shared Memory Management:
The manual Shared Memory management allows you to tune the individual parameters by specifying the size for each main pool.
Automatic Memory Management:
In this Automatic memory management, oracle database instance automatically manages and tunes the memory.
Choose the desired memory configuration option and enter the required fields.

Sizing:
Specify the number of Processes for operating system users that can be connected to the database simultaneously.

Character Sets:
Select the Character Set type carefully. It is difficult to change the character set after installation, and there are many restriction for character set conversion. AL32UTF8 supports almost all languages, so it’s recommended that you use AL32UTF8 as database character set.

Connection Mode:
Accept the default Dedicated Server Mode as it allocates dedicated resource for each client.

Sample Schemas:
Here, optionally you can add the Sample Schemas to your database. Click on Next button once you are done.

Specify Management Options

12. In this window, optionally you can Configure Enterprise Manager Database Express for your database and/or Register with Enterprise Manager Cloud Control. Click on Next button after making changes as per your requirement.

Specify Database User Credentials

13. Choose either Use different administrative passwords or Use same administrative passwords for all accounts and enter the password for administrative users. Click on Next button.

Select Database Creation Option

14. Select the Create Database option as we going to create a new database and click on Next button.

Summary

15. Now you will be presented with below summary screen. Read the database summary information, If you are happy with it click on Finish button to start the database creation.

Progress Page

16. The database creation progress will be shown as below. Please wait for it to complete.

Finish

17. Now after the successful database creation the below screen will be presented. Click on the Password Management button to view the list of locked user accounts. Now click on Close button to exit the installer.

This completes the Oracle 12c Release 2 or 12cR2 database creation.

Oracle Database 12cR2 Installation on Oracle Linux

By // 2 comments:
1. Introduction
2. Installation Requirements
3. Pre-installation tasks
4. Installation procedure
5. Database Creation

Introduction:

This article explains the step by step procedure to install Oracle 12c Release 2 (12.2.0.1) database software on oracle linux 6 x86-64 or redhat enterprise linux 6 x86-64 with pictorial representation.


Installation Requirements:

Below are the requirements for the installation of oracle 12cR2 database software.

Operating System Requirements:

Perform the Installation of Oracle Enterprise Linux 6 x86-64. Refer the Oracle Linux 6 installation Document for the Installation.

Hardware Requirements:

A. Please 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. Please verify it by using the below command.
# uname -m
B. The kernel requirements for Oracle Database 12c Release 2 should be 2.6.39-200.24.1.el6uek.x86_64 or later for OEL 6 and  2.6.32-71.el6.x86_64 or later for RHEL6. Check the kernel version of your linux machine by using the below command.
# uname -r
C. At least 1.0 GB of physical RAM. We recommend minimum 2.0 GB of physical RAM for smooth installation. Command to check amount of RAM installed on the system is,
$ free -m (or) $ grep MemTotal /proc/meminfo
D. A minimum of 2 GB swap space or more is required for the database installation. Swap size should be proportional to RAM size as follows,
Swap space equal to Double the RAM size (if RAM < 4GB)
Swap space equal to RAM size (if RAM > 4GB
Swap space equal to 16GB (If RAM More than 16GB)
Check the Swap size by using,
$ free -m (or) $ grep SwapTotal /proc/meminfo
E. Minimum of 1.0 GB free space in /tmp directory is required and maximum of 2 TB free space. To check the amount of free space in /tmp,
$ df -h /tmp
F. More than 7.5 GB free space is required on the mount point to install oracle 12cR2 database Enterprise Edition or Standard Edition. The recommended space for total database installation is to have more than 50 GB free space in the server for software and database.

Software Requirements:

Download Oracle 12cr2 database software for Linux x86-64 from www.oracle.com and copy the files to linux server using winscp or shared folder.

Pre-Installation Tasks:

A. Set selinux to either permissive or disabled by using the below command.
# setenforce 0
To permanently disable selinux, edit "/etc/selinux/config" and set value for SELINUX to "disabled".
B. Disable the firewall by using the below commands.
# service iptables save
# service iptables stop
# chkconfig iptables off
# service ip6tables save
# service ip6tables stop
# chkconfig ip6tables off
C. The shared memory (/dev/shm) should be sized to be at least the greater of MEMORY_MAX_TARGET and MEMORY_TARGET for each oracle instance. Change the size of /dev/shm by running the following command as root user.
# mount -o remount,size=4g /dev/shm 
Add following line in /etc/fstab file. Modify the value of size based on the amount of memory you will be using for your SGA.
tmpfs                   /dev/shm                tmpfs   default,size=4g        0 0
D. The "/etc/hosts" file must contain a fully qualified name for the server. Add the IP address and full hostname to /etc/hosts file in the following format,
<IP-address> <fully-qualified-host-name> <machine-name>
Example:
192.168.1.45   server1.dbacentral.com   server1
E. We can perform automatic configuration by using yum command. If you want to do manual configuration, skip this step and go to next step.
Execute following command to perform all prerequisites automatically. The below command will install all required packages which are needed for RDBMS software installation.
# yum install oracle-rdbms-server-12cR2-preinstall –y
Add the following lines to the /etc/security/limits.conf file.
oracle   soft   nofile    1024
oracle   hard   nofile    65536
oracle   soft   nproc    16384
oracle   hard   nproc    16384
oracle   soft   stack    10240
oracle   hard   stack    32768
oracle   hard   memlock    134217728
oracle   soft   memlock    134217728
F. Skip this step if you have followed above step, otherwise install the following packages manually. Install following packages (or latest version) from either yum repository or from Linux 6 media.
binutils-2.20.51.0.2-5.11.el6 (x86_64)
compat-libcap1-1.10-1 (x86_64)
compat-libstdc++-33-3.2.3-69.el6 (x86_64)
compat-libstdc++-33-3.2.3-69.el6 (i686)
gcc-4.4.4-13.el6 (x86_64)
gcc-c++-4.4.4-13.el6 (x86_64)
glibc-2.12-1.7.el6 (i686)
glibc-2.12-1.7.el6 (x86_64)
glibc-devel-2.12-1.7.el6 (x86_64)
glibc-devel-2.12-1.7.el6 (i686)
ksh
libgcc-4.4.4-13.el6 (i686)
libgcc-4.4.4-13.el6 (x86_64)
libstdc++-4.4.4-13.el6 (x86_64)
libstdc++-4.4.4-13.el6 (i686)
libstdc++-devel-4.4.4-13.el6 (x86_64)
libstdc++-devel-4.4.4-13.el6 (i686)
libaio-0.3.107-10.el6 (x86_64)
libaio-0.3.107-10.el6 (i686)
libaio-devel-0.3.107-10.el6 (x86_64)
libaio-devel-0.3.107-10.el6 (i686)
libXext-1.1 (x86_64)
libXext-1.1 (i686)
libXtst-1.0.99.2 (x86_64)
libXtst-1.0.99.2 (i686)
libX11-1.3 (x86_64)
libX11-1.3 (i686)
libXau-1.0.5 (x86_64)
libXau-1.0.5 (i686)
libxcb-1.5 (x86_64)
libxcb-1.5 (i686)
libXi-1.3 (x86_64)
libXi-1.3 (i686)
make-3.81-19.el6
sysstat-9.0.4-11.el6 (x86_64)
Edit the /etc/sysctl.conf and add following entries to set kernel parameters.
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
kernel.panic_on_oops = 1
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.conf.all.rp_filter = 2
net.ipv4.conf.default.rp_filter = 2
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
Execute following command after adding above lines in /etc/sysctl.conf file as root user.
# /sbin/sysctl -p
Edit the /etc/security/limits.conf file to set shell limits for oracle user.
oracle   soft   nofile    1024
oracle   hard   nofile    65536
oracle   soft   nproc    16384
oracle   hard   nproc    16384
oracle   soft   stack    10240
oracle   hard   stack    32768
oracle   hard   memlock    134217728
oracle   soft   memlock    134217728
G. Create the new groups and users by using below commands.
# groupadd -g 54321 oinstall
# groupadd -g 54322 dba
# groupadd -g 54323 oper
# useradd -u 54321 -g oinstall -G dba,oper oracle
Set the password for oracle user by using below command.
# passwd oracle
H. Create the required directories for Oracle Inventory, Oracle Base and for database software installation. Set the required permission on the created directories.
# mkdir -p /u01/app/oraInventory
# mkdir -p /u01/app/oracle
# mkdir -p /u01/app/oracle/product/12.2.0.1
# chown -R oracle:dba /u01/app/oracle
# chmod -R 775 /u01/app/oracle

Installation Procedure:

1. Now login as oracle user and invoke oracle universal installer (OUI) from oracle database stage location by using the following command.
$ ./runInstaller

Configure Security Updates

2. Now you will be presented with the below screen. Configuring the Oracle Support credentials, which are required for Oracle Configuration Manager. If you have valid credentials and the server is connected to the Internet, then you should provide these details. Otherwise, you can skip this option and configure them after installation.

Installation Option

3. Here Three options are available in this screen. As we are performing a fresh software installation, choose Install database software only option. Click on Next button.

Database Installation Options

4. Choose the Single Instance Database installation as we are going to perform the installation on single node. Click on Next button.

Database Edition

5. Select the database edition as Enterprise Edition and click on Next button.

Installation Location

6. Specify the path for Oracle Base and Software Location and click on Next button.

Operating System Groups

7. Select the desired operating system groups and click on Next button.

Prerequisite Checks

8. Now the installer will perform prerequisite checks and list the errors and warning. Please resolve the issues, before going to next step if you have any.

Summary

9. Now you will be presented with a Summary screen as below. Click on Install button to start the installation.

Install Product

10. The database software installation will be processed as shown below. Wait for the installation to complete.

11. Execute the scripts as a root user. Click on OK button after running the scripts.

Finish

12. The successful installation shows the below screen. Click on Close button to exit the installer.

This completes the installation of Oracle 12cR2 database software.

Oracle 12cr2 Database Creation

Read more at Oracle 12c Release 2 database creation document.

PRVF-0002: could not retrieve local node name

By // No comments:

Error Message:

While starting runinstaller for the installation of oracle database 12.2.0.1 on OEL 6 86x64, I encountered this "PRVF-0002: could not retrieve local node name" error.


Cause:

Finally, I found the cause that we have an incorrect entries in the "/ets/hosts" file.
[root@server1 ~]# hostname
server1.dbacentral.com

[root@server1 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain
::1         localhost localhost.localdomain
192.168.1.45       server1.oracle.com         server1
[root@server1 ~]#
The hostname of my linux machine and the information in the "/etc/hosts" file is different. Here my hostname is server1.dbacentral.com, but have an entry like server1.oracle.com.

Solution:

Verify the hostname of your machine by using the following command.
[root@server1 ~]# hostname
server1.dbacentral.com
[root@server1 ~]#
Verify the information of your server in "/etc/hosts" file. You should have same fully qualified ostname and IP Address.
[root@server1 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain 
::1         localhost localhost.localdomain
192.168.1.45       server1.dbacentral.com server1
[root@server1 ~]# 
Try to ping the server using fully qualified hostname.
[root@server1 ~]# ping server1.dbacentral.com
PING server1.dbacentral.com (192.168.1.45) 56(84) bytes of data.
64 bytes from server1.dbacentral.com (192.168.1.45): icmp_seq=1 ttl=64 time=0.073 ms
64 bytes from server1.dbacentral.com (192.168.1.45): icmp_seq=2 ttl=64 time=0.05
Now, Start the runInstaller again.
[oracle@server1 ~]$ ./runInstaller



How to ssh without password linux

By // No comments:

Introduction:

In your practice you may need perform ssh without password in linux or you may need to do passwordless scp between two linux machines. For that you need to setup an ssh auto login password between two servers. You can perform passwordless scp and passwordless ssh, by configuring ssh auto login password.

Perform the below steps to configure ssh passwordless login in linux. Here, i am explaining the password less connection between two nodes, server1 and server2.

ssh without password linux:

Connect to server1 and run the below commands as a administrative user to establish the ssh connection without password from server1 to server2.

Step1: Run the below command as administrative user with which you want to make passwordless ssh connection. Hit Enter button when it prompts. Here am establishing ssh as oracle user.
Syntax: ssh-keygen -t rsa
[oracle@server1 ~]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/oracle/.ssh/id_rsa): Enter⏎
Created directory '/home/oracle/.ssh'.
Enter passphrase (empty for no passphrase): Eneter⏎
Enter same passphrase again: Eneter⏎
Your identification has been saved in /home/oracle/.ssh/id_rsa.
Your public key has been saved in /home/oracle/.ssh/id_rsa.pub.
The key fingerprint is:
e6:fa:f6:92:0e:c9:50:b0:b2:03:a3:5a:a8:1c:58:1c oracle@server1.dbatalent.com
The key's randomart image is:
+--[ RSA 2048]----+
|  E .            |
| . . o           |
|o + . .          |
|o= o .           |
|+.+ .   S        |
|+... o +         |
|o.    + ..       |
|       o+        |
|      .+oo.      |
+-----------------+
[oracle@server1 ~]$

Step2: After generating public/private keys run the below command. Here Replace the IP address with your destination server IP(Here server2 for me). Enter the password for destinations server administrative user.
Syntax: ssh <Hostname / IP address> mkdir -p .ssh
[oracle@server1 ~]$ ssh 192.168.1.100 mkdir -p .ssh
oracle@192.168.1.100's password: 
[oracle@server1 ~]$

Step3: Now run the below command and enter the password for destination server user to copy authorized keys.
Syntax: cat .ssh/id_rsa.pub | ssh <hostname / IP address> 'cat >> .ssh/authorized_keys'
[oracle@server1 ~]$ cat .ssh/id_rsa.pub | ssh 192.168.1.100 'cat >> .ssh/authorized_keys'
oracle@192.168.1.100's password: 
[oracle@server1 ~]$ 

Step4: Now the below command to give the pemissions on authorized keys and enter the password when it prompts.
Syntax: ssh < hostname / IP address> "chmod 700 .ssh; chmod 640 .ssh/authorized_keys"
[oracle@server1 ~]$ ssh 192.168.1.100 "chmod 700 .ssh; chmod 640 .ssh/authorized_keys"
oracle@192.168.1.100's password: 
[oracle@server1 ~]$ 

Step5: Now verify the ssh passwordless login by making ssh connection from server1 to server2.
Syntax: ssh <hostname / IP address>
[oracle@server1 ~]$ ssh 192.168.1.100
Last login: Thu Feb 23 18:56:38 2017
[oracle@server2 ~]$ 
Now you are success fully completed ssh passwordless login. If you want make ssh passwordleass login from server2 to server1, then repeat the same 4 steps from server2.

ORA-48913 caught while writing to trace file, ORA-48913: Writing into trace file failed

By // No comments:

Error Message:

Non critical error ORA-48913 caught while writing to trace file,
ORA-48913: Writing into trace file failed, file size limit [10485760] reached Writing to the above trace file is disabled for now on...

Log file:

Tue Feb 07 13:05:18 2017
Non critical error ORA-48913 caught while writing to trace file "/u01/oracle/db/12.1.0/admin/DEV_dbacentral/diag/rdbms/dev/DEV/trace/DEV_ora_18128.trc"
Error message: ORA-48913: Writing into trace file failed, file size limit [10485760] reached
Writing to the above trace file is disabled for now on...

Solution:

Here,
File size limit = (MAX_DUMP_FILE_SIZE ) * (OS block size)
You can find the MAX_DUMP_FILE_SIZE and OS block size by using below queries.

If the file size limit reached to (MAX_DUMP_FILE_SIZE) * (OS block size) then the solution is to increase MAX_DUMP_FILE_SIZE to specific or unlimited.
Here,
5M = 5 megabytes each trace file, but could be any value or unlimited.
BOTH = Make the change at memory and spfile level at same time.
SID='*' = Because the change must be done in all instances.
You don't need to bounce the instances if you are using an spfile. If not, you can make the change at memory level by using SCOPE=MEMORY parameter.

TNS-12535: TNS:operation timed out and TNS-00505: Operation timed out

By // No comments:



Error Message:

TNS-12535: TNS:operation timed out and TNS-00505: Operation timed out.

Log file:

TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505

TNS-00505: Operation timed out
    nt secondary err code: 110
    nt OS err code: 0
 Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.x.xxx)(PORT=45678))

Solution:

We can identify these errors in log file frequently. This problem is due to the value of idle session timeout in firewall settings. If the client connection is idle for more than the idle session timeout then the firewall drops the connection.
The following parameter, set at the $ORACLE_HOME/network/admin/sqlnet.ora, can resolve this kind of problem.
SQLNET.EXPIRE_TIME=n  Where <n> is a non-zero value set in minutes. 
After adding this parameter no need to restart the database or listener. The new changes takes place automatically for new connections.

OS Health Checkup Script for DBA's

By // No comments:


Introduction:

This Linux shell script will perform the OS level health checkup and displays the output with  related warnings and errors.

Description:

This script will give the output for below.

1) OS information:
Operating system information like OS type, OS version, OS bit version , Kernel version and hostname.

2) Shut down, reboot and up timings:
The system last shutdown time, rebooted time and total uptime of the system.

3) Load average on the system:
The load averages on the system in last 1,5 and 15 minutes.

4) Number of processes:
The details of number of processes on the system.

5) CPU Usage:
The system idle CPU percentage will be displayed and gives alert message if the idle CPU is below 10% and warns if the idle CPU is below 20%.

6) Memory and Swap usage:
Memory and SWAP usage will be displayed and gives the warning if the usage is above 80% and alert if the usage is above 90%.

7) Mount point space usage:
Mount points usage information will be displayed and gives the warning if the any mount point fill to 80% and alerts if the usage is above 90%.

8) Errors messages from /var/log/messages:
It will list the error , warning and failed messages from /var/log/messages.

Platform:

This script is for Linux/Unix Generic platforms. Applicable to OEL 6 or RHEL 6 and below versions of Linux and Unix. 

Download the Script:

Click here to download the shell script.

Usage:

Run this shell script as root user.
Note: We recommend you to run this script in test environments before proceeding to production environment.

# sh os_healthcheck.sh


Output:

The out put of the script will be as shown below.