Below are the questions asked to a candidate with 10 years of experience in Oracle DBA and postgres . There were 2 rounds.

Round 1 :

•  What are your day to day activities. And what is your role in the Team?
• Explain cluster startup sequence.
• Suppose users are connected to node1 and Os team, want to do emergency maintenance and they need to reboot. What wil happens to the transactions on node1 , and can we move them to node 2.
• What are the advantages of partitioning?
• Can we convert non-partitioned table to partitioned table online( in production)?
• What are the cloning methods. And what is the difference between duplicate method and restore method?
• How can we open the standby database as read write for testing and Once testing is done need to revert to previous position. But without using snapshot command?
• Explain how you did grid upgrade.
• Which advanced security features you have used ?
• What is audit purging?
• Lets says a query was running fine for last few days , but today it is not performing well. How you will troubleshoot it?
• What is oracle in memory ?
• Explain what type of patches you applied on grid.
• As per resume ,You have worked on ansible . So how good you are with ansible and what type of scripts you have wrote.
• How can you troubleshoot a shell script?

2nd round:

• Explain ASM architecture , means when ever user runs a query, how it gets the data from ASM.
• Why ASM performance is better
• Why VIP is used , Bcoz before 10g vip was not used. What benefits vip provide
• What is Current block and CR block in RAC
• What wait events are there in RAC??
• What is gc buffer busy wait
• What is gc 2 way current wait, gc 3 way current wait.
• What is node eviction. In which cases it happens.
• How you do load balancing using services in your project. Explain
• What activities you did using goldengate
• How you did upgrade/migration using goldengate.
• What is goldengate instantiation.
• What is bounded recovery in goldengate
• What is the difference between CSN in goldengate vs SCN in database?
• Which type of os tool/commands you have used for os monitoring/performance. And how you read it.
• How you troubleshoot I/O issues in oracle
• What type of sql performane tools you use.
• How you migrate a plan from one db to another db
• What is the dbms package for baseline and sql tuning?
• Lets say, despite importing the baseline, query is not picking the plan, What can the be reason?
• What is index fast full scan and index range scan?
• What is the purpose of standby redolog.
• Any automation tools you have used?
• Did you worked on jenkins or gitlab?
• What activities you did in postgres.
• How you implement backup in postgres.
• Is your postgres cluster active active or active passive?
• Can we create a active active postgres cluster?
• Do you remember any postgres extensions?
• What type of scripts you have written on ansible?
• Tell me some modules of ansible.
• How can we encrypt the server credentials in ansible.

If you wish to share your interview experiences, then please drop a mail to support@dbaclass.com. We will happy to publish it with your permission.

The post Real Interview questions for experienced DBAs – 1 appeared first on DBACLASS.

1. Check the existing port details

postgres=# select * from pg_settings where name='port';
-[ RECORD 1 ]---+-----------------------------------------------------
name            | port
setting         | 5444
unit            |
category        | Connections and Authentication / Connection Settings
short_desc      | Sets the TCP port the server listens on.
extra_desc      |
context         | postmaster
vartype         | integer
source          | configuration file
min_val         | 1
max_val         | 65535
enumvals        |
boot_val        | 5444
reset_val       | 5444
sourcefile      | /pgdata/data/postgresql.conf
sourceline      | 63
pending_restart | f


postgres=# \conninfo
You are connected to database "postgres" as user "enterprisedb" via socket in "/tmp" at port "5444".



postgres=# show config_file;
           config_file
---------------------------------
 /pgdata/data/postgresql.conf
(1 row)

[enterprisedb@master ~]$  cat /pgdata/data/postgresql.conf | grep 'port'
port = 5444                             # (change requires restart)

2. Update the port in postgresql.conf file:

-- change the port from 5444 to 5432

[enterprisedb@master ~]$  cat /pgdata/data/postgresql.conf | grep 'port'
port = 5432 

3. restart postgres services:

pg_ctl stop -D /pgdata/data
pg_ctl start -D /pgdata/data

Alternatively you can restart the service, if configured.

root# systemctl stop edb-as-11
root# systemctl start edb-as-11

4. Check whether port has been updated

[enterprisedb@master ~]$psql -d postgres -p 5432

postgres=# \x
Expanded display is on.

postgres=# select * from pg_settings where name='port';
-[ RECORD 1 ]---+-----------------------------------------------------
name            | port
setting         | 5432
unit            |
category        | Connections and Authentication / Connection Settings
short_desc      | Sets the TCP port the server listens on.
extra_desc      |
context         | postmaster
vartype         | integer
source          | configuration file
min_val         | 1
max_val         | 65535
enumvals        |
boot_val        | 5444
reset_val       | 5432
sourcefile      | /pgdata/data/postgresql.conf
sourceline      | 63
pending_restart | f

We can see , the port has been updated to 5432.

Now If any streaming replication is enabled, then we need to update the primary server  port in recovery.conf file of standby server.

5. Check for any streaming replication ( run On primary server)

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+---------------------------------
pid              | 2800
usesysid         | 10
usename          | enterprisedb
application_name | walreceiver
client_addr      | 10.20.30.77
client_hostname  |
client_port      | 45884
backend_start    | 01-JUN-21 09:38:07.003029 +03:00
backend_xmin     |
state            | streaming
sent_lsn         | 0/F001AB8
write_lsn        | 0/F001AB8
flush_lsn        | 0/F001AB8
replay_lsn       | 0/F001AB8
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async

It shows replication is enabled to server 10.20.30.77(standby server). So we need to update the recovery.conf file in that standby server.

6.Update the recovery.conf file in standby server.

-- recovery.conf file resides inside data directory.
[enterprisedb@standby]$ cat /pgdata/data/recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=enterprisedb password=edbpostgres#123 host=10.20.30.76 port=5432 sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any'
primary_slot_name = 'slot1'

7. Updating the postgresql.conf file:

Just like primary, if you want to change the listening port from 5444 to 5432 in standby( just like primary) also, then update the postgresql.conf file in standby server also. Otherwise  you can continue with the same port.

[enterprisedb@master ~]$  cat /pgdata/data/postgresql.conf | grep 'port'
port = 5432                             # (change requires restart)

7.Restart the pg services in standby server.

pg_ctl stop -D /pgdata/data
pg_ctl start -D /pgdata/data

Alternatively you can restart the service, if configured.

root# systemctl stop edb-as-11
root# systemctl start edb-as-11

8.Check replication status on standby:

postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 8124
status                | streaming
receive_start_lsn     | 0/D000000
receive_start_tli     | 1
received_lsn          | 0/F001AB8
received_tli          | 1
last_msg_send_time    | 01-JUN-21 16:59:57.746814 +03:00
last_msg_receipt_time | 01-JUN-21 16:59:57.747272 +03:00
latest_end_lsn        | 0/F001AB8
latest_end_time       | 01-JUN-21 09:54:24.322036 +03:00
slot_name             | slot1
sender_host           | 10.20.30.76
sender_port           | 5432
conninfo              | user=enterprisedb password=******** dbname=replication host=10.20.30.76 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any

The post How to change port number in postgres appeared first on DBACLASS.

Make sure flashback is enabled:

SQL> select flashback_on from v$database;
 
FLASHBACK_ON
------------------
YES

If flashback is not enabled Refer: How to enable flashback in oracle

Delete some data:

09:10:51 SQL> select count(*) from test;

  COUNT(*)
----------
     87298


09:13:11 SQL> delete from test;

87298 rows deleted.

09:13:36 SQL> commit;

Commit complete.

09:13:39 SQL> select count(*) from test;

  COUNT(*)
----------
 0

Now use timestamp to_date to query the table in past(for particular time stamp)

09:15:53 SQL> select count(*) from test AS OF TIMESTAMP TO_DATE('29-AUG-2015 09:10:51','DD-MON-YYYY HH24:MI:SS');

  COUNT(*)
----------
     87298

Restore the deleted data

09:18:16 SQL> insert into test select * from test AS OF TIMESTAMP TO_DATE('29-AUG-2015 09:10:51','DD-MON-YYYY HH24:MI:SS');

87298 rows created.

09:19:38 SQL> commit;

Commit complete.

We can see by using flashback query, we are able get the deleted data from the past.

The post How to use flashback query in oracle database appeared first on DBACLASS.