How to establish ssh equivalency between two servers

To establish a passwordless ssh between servers,first generate a public/private RSA encryption key using the command:

step 1: The key of the host is updated on files ‘id_rsa’ and id_rsa.pub with key value pairs

oracle@orcl ~]$ssh-keygen -t rsa

Enter the passphrase:

step 2: Copy the symmetric keys to the authorized_keys file

oracle@orcl ~]$cd .ssh

oracle@orcl .ssh]$ cat id_rsa.pub | ssh hostname ‘cat >> /home/oracle/.ssh/authorized_keys’

step 3: Try to ssh from one node to other and vice versa

oracle@orcl ~]$ ssh hostname1 && ssh hostname2

FRA space full oracle

we often as a dba run into trouble ,when flash recovery area of one of critical database gets full due to bulk DML as a cause of business transactions.

This causes the database to hang and cause impact to the applications.Though ,it is not in our hands as we cant control it until the architecture .

Hence a good monitoring system should be implemented to overcome these situations.

The parameter set for FRA is db_recovery_file_dest_size

If the parameter is not set sufficient to balance the archivelog generations ,then database get issues.

We get “ORA-12154 ” and “ORA-00257: archiver error”

SQL>show parameter db_recovery_file_dest_size;

db_recovery_file_dest_size 100G

check the reason for the FRA space full and free space using below useful views

SQL>select * from v$flash_recovery_area_usage;

SQL>select * from v$recovery_file_dest;

To resolve this error,we can either increase the FRA or delete the archivelogs from disk which are older.

Solution 1:

SQL>alter system set db_recovery_file_dest_size=200G;

Solution 2:

Connect to RMAN ,then we can delete the archivelogs which are older.If backups are taken every 12 hours in database we can consider deleting archivelogs generated before 1 day.

You can delete obsolete,expired backups which are no longer needed for recovery to clean up space.You can write a shell script to monitor the space for FRA and make them clear automatically which we will see in the future posts.

In case you have a standby database ,then archivelog deletion policy can be enabled in RMAN configuration.

~]rman target /

run { 
configure archivelog deletion policy to applied on all standby;
allocate channel "c1" device type disk; 
allocate channel "c2" device type disk; 
crosscheck backup; 
crosscheck archivelog all; 
delete noprompt expired backup; 
crosscheck archivelog all; 
delete noprompt expired archivelog all; 
delete noprompt obsolete recovery window of 30 days; 
backup archivelog all delete input; 
crosscheck archivelog all;
delete noprompt archivelog until time 'sysdate-1' backed up 1 times to device type disk; 
}

You can also run periodic archive backups and delete them.Put backup configuration script in crontab to run every hour

something like this

~]crontab -e

0 */1 * * * /home/oracle/archivebackup.sh >/dev/null 2>&1

The maximum that you can do to clear FRA are above solutions. You can notify your application user to batch jobs during off peak hours to avoid bulk transactions concurrently.

Database Blocking Sessions

In oracle, we often fall into performance issues due to problems inherited from sessions which block each other.

For a dba, blocking is often a problem which should be identified and solved with the confirmation from application team.

This is due to DML transactions on the table rows and locking the table to avoid other users modifying the table until the first user issues commit.

Hence oracle has a very good feature called Multiversion concurrency control(MVCC)

Sounds confusing ?Lets give an example to understand.

Say there are two users test1 and dev1.

I create a new table called emp and inserting some values to it.

SQL>create table emp( emp_id INTEGER NOT NULL PRIMARY KEY, emp_name VARCHAR2(100) NOT NULL, sur_name VARCHAR2(100) NOT NULL, role VARCHAR2(20), dob DATE )

SQL>insert into emp values(1, 'abc', 'cba', 'b', dr'1990-04-10')

SQL>insert into emp values(2, 'xyz', 'zyx', 'y', dr'1999-09-18')

Then select the rows from the table and two rows created under emp table.

SQL>select * from emp;

Create two test users.

SQL>create user test1 identified by password;

SQL>create user dev1 identified by password;

grant emp table access to the users test1 and dev1.

SQL>grant connect,resource,select,insert,update on sys.emp to dev1;

SQL>grant connect,resource,select,insert,update on sys.emp to test1;

connect to test1 user

SQL>conn test1

update role column on emp table

SQL>update emp set role=eng where emp_id=2;

connect to dev1 user and update same column with different value

SQL>conn dev1

SQL>update emp set role=eng1 where emp_id=2;

Now two users are trying to modify the same column and same row

The test1 user modified the rows and not commited the transaction

This creates a lock on the table until the user issues a commit

when dev1 user also modify the role column where emp_id=2 ,the session gets hung until test1 user releases the lock with commit.This also causes performance issues if multiple DML transactions occur

identify the blocking sessions with query

SQL>select sid,serial#,username,blocking_session,wait_class,seconds_in_wait
from
v$session;

Warning !!! : In production databases ,once there is a confirmation from application team we can kill the sessions. Dont kill the session without any proper clarity.

Get the sid and serial# from v$session and apply them

SQL>alter system kill session '&sid,&serial#';

Once the test1 user session killed ,the dev1 user session can modify the row and should issue a commit

“””This post is for learning purpose don’t blindly implement in production”””

Python jumbled word

The below message is encrypted with a programming language and its datatype. The word is arranged in a serial order combining the two words. You have to isolate the letters and form a word in given sample output.If the string length is different ,you have to add a ‘*’ to replace the empty space and remove the symbol. Write a python code to identify and print the results.

The message is ‘ptyutphloen’

Sample Input:

ptyutphloen

Sample Output:

Python,tuple

Solution:

word = 'ptyutphloen'
''.join(word[0::2].rstrip('*') + ',' + word[1::2].rstrip('*'))

Oracle interview questions

When you enter the interview panel, the rule of thumb question asked by every interviewer is to introduce yourself.I have just mentioned a sample introduction.There is again no thumb rule to answer the question in one way.There are multiple ways of answering this question based on each perspective.

1)Tell me about yourself,your activities in your day to day life as a dba

I am basically a database admin with x years of experience.

I handled over 100 oracle servers with 200 databases and 300 instances in my previous projects.

I have good experience on installation,configuration,upgradation,migration,user management,tablespace management,patching,cloning,backup,high availability solutions like RAC,dataguard,performance tuning and so on …

2)Can you explain about the architecture of oracle database 12c?

The instance is called memory + background processes like SGA and PGA.Also UGA depending on shared servers.

SGA consist of shared pool,database buffer cache,redo buffer,large pool,java pool,streams pool.

Database consists of physical files like datafiles,online redolog files,controlfiles,archivelog files etc.

Mandatory background processes: SMON,PMON,DBWR,LGWR and CKPT

3)What is SMON?

smon manages instance crash recovery and clean up the temporary segments.

4)What are the modes of database buffer cache?

Free(unused),pinned(held) and dirty buffer(modified)

5)How to calculate the size of database?Mention the views?

dba_segments, dba_extents, v$datafile, dba_data_files, dba_temp_files, v$tempfile, dba_free_space

6)How to calculate the memory for a database?Mention the views?

select sum(value)/1024/1024/1024 “SGA_SIZE (GB)” from v$sga;

select sum(pga_max_mem)/1024/1024/1024 “PGA_SIZE (GB)” from v$process;

7)How to check the resource consumption (CPU,memory) of a process in a Linux server?

TOP command,free -mg, mpstat,sar,vmstat

8)What is the state of redolog when new member is added to redolog?

Unused

9)If one of the controlfile is corrupted,what happens to the database?

we get error “ORA-00205: error in identifying control file, check alert log". Recent controlfile needs to be restored from backup.

10)For a database ,how many recovery catalog can be present?

we can have multiple catalogs for a database.

But multiple databases can register to a single catalog.

11)explain about the sql processing in oracle?

Whenever sql statement is executed ,it has to enter the library cache into shared pool and the statement is parsed.

The parse has to undergo syntax check and semantic check.

The optimization has to generate execution plan for the statement with hash value.

Then the result is retrieved from the object.

12)What will you do if a user complains performance issues on the particular sql query?

fetch the sqlid using v$sql and v$session.use sql tuning advisor to get the advise.You can add index to the predicate column for example as per the advise.

13) how to show all the nodes in a RAC cluster?

olsnodes -nip

13)How to check if dataguard is running in a database?

v$dataguard_status, show parameter log_archive_Dest_2

14) How to check if mrp is running in physical standby?

v$managed_standby and ps -ef | grep mrp on standby

15)Things to check on awr report for performance issues?

dbtime ,elapsed time,top five foreground events by CPU time ,elapsed time and wait events- db_file_sequential_read,db_file_scattered_read etc.Please refer online resources to get more details on these jargons

Mount and Unmount NFS in Linux

NAS(network attached storage) mount point is a remote storage attached to a server.

The system administrator can access and share the files using NFS(network file system) protocol over network.

1)Check the available mount points on your server

~] df -h

2)Check if nfs service is running

~]service nfs status (To start the nfs service – service nfs start)

3)Check the nfs mount points

~]showmount -e a01nfs01

4)Create the directory structure to mount the nfs

~] mkdir /home/oracle/nfs

5)Give permissions to the directory

~] chown -R oracle:oinstall /home/oracle/nfs

6)Mount the nfs

~] mount -t nfs a01nfs01:/st01/orcl1/client/nfs /home/oracle/nfs

7)To permanently mount the nfs mount point,add the nfs entries in fstab

~]vi /etc/fstab

8)Add the following entry

a01nfs01:/st01/orcl1/client/nfs /home/oracle/nfs nfs\ rw,bg,hard,nointr,rsize=100000,wsize=100000,tcp,vers=3,timeo=600,actimeo=0,noatime 0 0

9)After adding the following entry,server should be rebooted in order to take permanent effect

Warning!! don’t blindly run the below command.The server requires downtime.Hence you have to plan before reboot,if the server is in production.

~] shutdown -r now

10)To unmount the nfs

~]umount /home/oracle/nfs

11)To permanently unmount nfs,comment the below entry and save

#a01nfs01:/st01/orcl1/client/nfs /home/oracle/nfs nfs\ rw,bg,hard,nointr,rsize=100000,wsize=100000,tcp,vers=3,timeo=600,actimeo=0,noatime 0 0

Thank you !!!

Shell scripting basics

We all sometimes feel that there can be some machine which can do your work so that you can concentrate on more innovations. Shell scripting is one of the powerful techniques to automate your daily tasks.In Linux, bash scripting is one of the common shell which we can invoke from the command line.

We are going to see how to create,edit and execute a shell script in Linux.

We can use a vi editor to write a shell script.

In Linux terminal,give the following:

~]$ vi file_name

Every bash script starts with the line:

#!/bin/bash

where #! is called sharp + bang also called as shebang for invoking the bash executable from binary.

For example: I want to clean up the logfiles using a shell script for specific log directory for a every two days.I write a shell script like:

#!/bin/bash

find /path/to/files/ -type f -name '*.log' -mtime +2 -exec rm {} \; —-> give ‘shift key’ + : + wq to exit the vi editor

Give permissions to the file to execute:

chmod -R 755 filename

To execute a shell script:

use “./ + filename”

example: If your file is present in current working directory you have to use “./”

./filename

if your file is present in some other directory ,then set the variable $PATH as :

export PATH=/path/to/executable file

eg:export PATH=/bin/sqlplus

To check the path of the executable file

echo $PATH

We will see more in further posts.Thank you!!!

ORA-01652: unable to extend temp segment by 128 in tablespace users

You find this ora-01652 error often ,when there are huge sorts like group by ,order by and death hash joins which has large sort segments utilizing the PGA and spill over temporary tablespace to save the intermediate result sets happening in the database instance. If PGA is not sufficient to handle the large sorts in memory itself, then intermediate result sets undergo single pass or multipass operation which will undergo sorts in disk and much slower than PGA sorts

Often application administrators have this misconception that adding more space with tempfiles to temp tablespace or having huge temp tablespace will cause the sql queries to run like jet speed. This is not always true because if the sql query doesnot have sorts or group by or hash aggregation works ,then there is no point in adding more tempfiles to temporary tablespace. This will be like adding more oil to fire instead of water. You should know what consume what!

For example: If you want your vehicle to run fast, then instead of filling the fuel tank with water, fill more fuel to it. Adding water will cease your vehicle to run. Likewise, if the sql query (vehicle) with heavy sort want to run fast, then add more PGA and TEMP(fuel) to your database. 😉

An application administrator who ask for more temp space to add without having idea on the sorting operations on the query so that they can get maximum database speed.

check the size of the temporary tablespace

kish@exdbx<>col TABLESPACE_NAME format a10
col TABLESPACE_SIZE format a20
col TABLESPACE_SIZE format 999999
col ALLOCATED_SPACE format 999999
col FREE_SPACE format 999999
select TABLESPACE_NAME,TABLESPACE_SIZE/1048576 MB_TOT,ALLOCATED_SPACE/1048576 MB_USED,FREE_SPACE/1048576 MB_FREE from dba_temp_free_space;kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>


TABLESPACE                                   MB_TOT                                  MB_USED                               MB_FREE
---------- ---------------------------------------- ---------------------------------------- ----------------------------------------
TEMP                                            355                                      355                           332

I use some sql queries with large sorts which use temporary tablespace for performing its sort operations

SQL> select a.country,a.item_type,a.sales_channel,b.order_date,sum(c.total_revenue),b.total_cost,b.total_profit
  2  from test.SALES_TAB_COPY a
  3  inner join test.tabx b on a.ORDER_ID=b.ORDER_ID
  4  inner join test.sales_tab c on b.order_id=c.order_id
  5  group by a.country,a.item_type,a.sales_channel,b.order_date,b.total_cost,b.total_profit
  6  order by 1,2,3,4 desc;

Monitor the sort utilization

kish@exdbx<>select USERNAME,SQL_ID,TABLESPACE,BLOCKS*8192/(1048576) SORTMB,SEGTYPE from v$tempseg_usage;

USERNAME        SQL_ID        TABLESPACE                                        SORTMB SEGTYPE
--------------- ------------- --------------- ---------------------------------------- ---------
TEST            NULL          TEMP                                                  12 HASH

Solution:

1)SMON background process cleans up the temporary segments and extents automatically

2)Add a new tempfile to the temp tablespace

SQL> alter tablespace temp add tempfile '/home/oracle/oradata/temp02.dbf' size 500M;

3)Set autoextend on for temp tablespace to avoid exhaust errors

kish@exdbx<>alter tablespace temp add tempfile '+DATA' size 5M autoextend on NEXT 5M MAXSIZE 500M;

Tablespace altered.

4)Increase PGA_AGGREGATE_TARGET at session level to avoid one pass and multipass IO’s

5) As a proactive measure to avoid temp errors, a dedicated temp tablespace with big space for the user who always run sort queries on database

A testcase

I was running a query with large sorts due to multiple join conditions and end up with the error ora-01652

SQL> /
select a.country,b.item_type,c.sales_channel,b.total_cost
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

When i kept monitoring the session for temp usage , i can find direct path write temp due to lack sufficient PGA.

USERNAME      SQL_ID     TABLESPACE SPID                     SORT_CNT   SID   PROGRAM         SERIAL# EVENT            USEDMB
---------- ------------- ---------- ------------------------ -------- ------- --------------- ------- --------------- -------
TEST       gqs10z3mw86h4 TEMP       13327                           2      73 sqlplus.exe          23 direct path wri    2767
                                                                                                      te temp

TEST       gqs10z3mw86h4 TEMP       13327                           2      73 sqlplus.exe          23 KSV master wait    2797

kish@exdbx<>select FILE_ID,TABLESPACE_NAME,BYTES/1048576 AvailMB,USER_BYTES/1048576 utilizeMB,MAXBYTES/1048576 MaxMB from dba_temp_files;

 FILE_ID   TABLESPACE_NAME  AVAILMB UTILIZEMB    MAXMB
---------- --------------- -------- --------- --------
         1 TEMP                2775      2774    32768
         2 TEMP                  15        14      500
         3 TEMP                  15        14      500

kish@exdbx<>select USERNAME,TABLESPACE_NAME,BYTES/1073741824 AvailGB, MAX_BYTES/1073741824 MaxGB from dba_ts_quotas;

USERNAME   TABLESPACE_NAME                                            AVAILGB                                              MAXGB
---------- --------------- -------------------------------------------------- --------------------------------------------------
OLAPSYS    SYSAUX                                             .00860595703125                   -.000000000931322574615478515625
SYSMAN     SYSAUX                                             .08258056640625                   -.000000000931322574615478515625
SQLTXADMIN EXAMPLE                                                          0                   -.000000000931322574615478515625
APPQOSSYS  SYSAUX                                                           0                   -.000000000931322574615478515625
FLOWS_FILE SYSAUX                                                           0                   -.000000000931322574615478515625
S

SQLTXPLAIN EXAMPLE                                             .0050048828125                   -.000000000931322574615478515625
PERFSTAT   SYSAUX                                               .000732421875                   -.000000000931322574615478515625

7 rows selected.

ASMCMD> lsdg DATA
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  4194304     12000       44                0              44              0             N  DATA/

ORA-01950 – GIVE USER QUOTA TO TABLESPACE

We created new tablespace but not given quota for the user. Would that user be able to create extent on that tablespace? Let us see!!

SQL> create tablespace newtbs datafile '/u01/oracle/oradata/new01.dbf' size 5G;

SQL>create user new identified by password;

SQL> grant connect,resource to new;

SQL>create table newtab (emp_id number,emp_name varchar(10));

SQL>insert into newtab values(1,'xyz');

We get this error.

“ORA-01952 : system privileges not granted to 'tablespace'“

Check if the tablespace exist under user ‘new’

select tablespace_name,
max_bytes/1024/1024/1024 "quota_in_gb"
from dba_ts_quotas
where username = 'new';

Solution:

SQL>alter user new quota 500m on newtbs; (unlimited also can be specified)

SQL>grant unlimited tablespace to new;

Thanks!!!

DATA COPY USING SQL*PLUS COPY COMMAND

Most of the Oracle dba’s would probably might not be using sqlplus copy command which is a very efficient way to copy small table data and columns from one database to another.

Copy usage:

Copies data from a query to a table in the same or another database. COPY supports CHAR, DATE, LONG, NUMBER and VARCHAR2.

COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, …)] USING query

we can set the following functions:

set copycommit 0 (number of copy batches after completion the changes are commited)

set arraysize 15(number of rows in batch)

1)COPY TABLE USING SQLPLUS:

copy from system/password@db1-

replace test1.tbl0101-

using select * from test.tbl0101

2)COPY COLUMNS FROM ONE TABLE TO ANOTHER TABLE:

copy from test1@db1-

to test2@db2 -

create tab2 (emp_id,emp_name,salary) -

using select * from tab1

3)COPY TABLE TO REMOTE DATABASE (add the tns entries of databases in tnsnames.ora)

copy from test1@db1 -

create tab2 -

using select * from tab1

Note: If you copy big size table,then increase your undo tablespace size and undo retention accordingly and monitor for archivelog and tracefile generations.If you have less space in mountpoint then you might end up filling the space fast.