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('*'))

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

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 !!!

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!!!

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

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

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

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!!!

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.

Troubleshooting the ssh equivalency issues in Linux:

I try to establish ssh equivalency connection between two servers for oracle user in Linux. I get the below error.

“CONNECTION CLOSED BY 192.168.56.108”

Solution:

vi /etc/security/access.conf

#All other users should be denied to get access from all sources.

+ : root oracle : ALL

After adding the entry of ‘oracle’ user on the above line in access.conf file,the connection was successful.

[oracle@orcl~]$ ssh orcl1 && ssh orcl

Issue is resolved!!

Tips to resolve the issue:

check /var/log/secure logfile to check for any errors.In my case, I got the below error.

“Access denied for user ‘oracle’ from ‘host’“

“Access denied for user ‘oracle’ by PAM account configuration’“

check for /etc/hosts.deny file ,if there are any ipaddresses restricted.

Check if selinux is blocking connections >> should be disabled (setenforce 0)

[oracle@orcl~]$getenforce