- RAC
What is SCAN & its benefit
What are voting disks & why we need to maintain odd
number of voting disks?
What are different disk redundancies available within ASM?
How do you backup ASM?
How can you configure multiple RAC databases on single ASM
How do perform health check of RAC databases
What are different components of RAC
What is difference between RAC on exadata vs non-exadata
servers
What are different tuning techniques for RAC on exadata
How do you troubleshoot RAC issues.
How to troubleshoot gc_wait events in a RAC Database?
How does the RAC Interconnect setup differ from a single
node to multi-node RAC Database?
What is a FAN event and how does it work with an application
code?
Cluser service start up sequence.
- DG
How do you configure dataguard? High level steps
Standby is out of sync with primary because of network
issues. How do you resynchronize with primary database again using both manual
& automatic techniques
How do you sync standby using incremental backups from
primary
How do you avoid archive logs getting deleted from primary
before applied to standby.
How do you restore archive logs from tape?
- GG
What are main components of the GG?
What transaction types golden gate support for replication?
Why is supplemental logging required for GG?
When do you implement bu-directional replication?
What are different captures?
What are day to day GG issues and how do you troubleshoot
them?
How do you check parameters of a process?
- Exadata
What are different layers of exadata
What flash cache & how it works
Exadata sizing configuration
How do you apply patches in exadata
What are the exadata health check tools available?
What are the high level steps involved in exadata config
What is infiniband?
- General Performance Tuning
When do you create sql baseline?
When do you create sql profile?
Difference between sql baseline & sql profile
How do you migrate sql baseline & sql profile between
databases
Have you used RAT capture/replay to measure performance
improvement/degradation of a change before moving to prod?
What do you look in AWR for performance bottlenecks?
How do you improve performance of SQL? Outline some
techniques.
How do you improve performance of a DML statement
- RMAN and backups
How do you backup & restore a database
How do you perform rman duplicate to refresh non-prod
environment using production database
How do you performan rman duplicate from tape backups
How do you perform rman duplicate using disk backups?
What is restore point?
How can you rollback database using restorepoint backups?
How do you recover a lost controlfile, lost log file, lost
datafile?
What are the different hidden db parameters that you worked
on?
Have you worked on multi-tenancy and other 12c features?
Whats the difference in exp and expdp .
Database Object
Whats Raw changing and Raw migration .
Database Object
Whats Raw changing and Raw migration .
=====================================
Which backgrond proceess writes to logfile -- almost all process.
can we have 8kb and 16kb blcoks in databse.
opeb cursond and curson sharing
Database wait events
what happens when we start nomount
expired backup and obsolete backup difference
How to forcefully assing a hashplan to sql_id
You need to use SQL baselines to force the execution plan.You'll need the
sql_id for the statement and the plan_hash_value of the old plan.three wait events that you fixed
where does client things stores in database.
How ot check history of base plan - dba_hist_Sqlstat and dba_hist_snapshot
checkpoint queue in oracle
====================================
import sql_file == file ane metadata only
worker process and all
asm backgroudd porcess
ps -ef | grep -i ASM
gmon ,rebalance ,lck etc
asm we required grid
%u is for parallel
Index and bitmap inde and tuyps
=======
+++
lock/block and deallock
Where update in a row in table .. then row exlusive lock with lock mode 3
IF any otehr session tries to update another colm for the same row it will create block in database.
blocking can be overcome by pasing commit for the 1st sessio /dba can realse the session from back end.
deallock
oracle identifies it and fix it internally/ It is like cross blocknig
One session tries to update one row in table and other sesison tries to update the another row in same table.
without giving commit. Now if 1st session tries to update the same row it will be deallocked. as commint hsa not given yet
+++++
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; hangs
difference between instance recovery and media recovery?
diffecnr in rollfoewards and rollback?
whats the exact diff in log file sync and log file parallel write wait evevnts
=============================================
10g and 11g alert/trace file location -- 11g all comes to one place diag 10g --dump diff location
Standby
physical standby and logical stanby - refresh snapshot standby
physical standby - db in read only
logical stanby - db in read write
rfs process in standby -
remote file server getting the redos from the primary database ,archive log ifles
How to rebuild standby --
Using increment rman back
If we add datafile in primary and it doest reflect in statndby what things will be looked for
primary tablespace filled
standby file management parameter -- if we set it auto then automatically it takes care
Why do we use dba file name conver and log file name convert
in the cloning -- if we provide diff locaiotn and name
Cloinng
manual and Rman cloning steps
neew to create control file and spfile based upon the primary/productin databsae
need to set database parameter db file convert and log file convert ,cehck db runing arvhigl ro non arhive
cretae one more tns for dbclone and neew to check the listener availablity first
restartor throug h rman using created control file
duplicate tartget database to new database;
Patching
to rectify the bugs
what kind of patcing in oracle - cpu and psu patches
Should we take the PSUs or the BPs? RURs or RUs?
And the answer is very simple:
- If you have an Oracle Engineered System: Take the Bundle Patches for Engineered Systems
- In all other cases:
- If you are on Oracle Database 12.2.0.1 or newer: Take the Upgrades (RU)
- If you are on Oracle Database 12.1.0.x: Take the Bundle Patches (BP)
- If you are on Oracle Database 11.2.0.4: Take the Patch Set Updates (PSU)
- If you are on a release below Oracle Database 11.2.0.4: Upgrade!
diag is not there in 10g
56 backgroup new process introduced in 11g memory ,resouce maanger ,snapshot standby ,to update the databsaue notification while user remani online
Archigge
what does SGA conss of
SGA - library cahche, dictonary cache, rado log buffer cachen,database buffer cache,jaga pool ,large pool, backgrou proce pmon,smon stroed in sga
what is user process and server process where these proces gets created
oracle itself creates both the user and server process
when user request a connection ( like connecing using sql dev) oracle will crete a user process -- v$session process
when user fires up any query then oracle creates a server process .. get pid from sid ..
PGA
programable global area...
What is SCN and when it gets genered and its signifian
its system change number
its stored in control file
the system change number (SCN) is Oracle's clock - every time we commit, the clock increments. The SCN just marks a consistent point in time in the database.
a commit does not write to datafiles at all. it does not update control files at all.
scn's are written to redo logs continuously - when you commit they are emitted into the redo stream.
A checkpoint is the act of writing dirty (modified blocks from the buffer cache to disk.
In which instance SCN gets generated
What is shared pool--
Performacne-
what do you mean by cost in execution plan ?
What is optimizer mode in the databse? CBO n RBO
LRU and MRU difference
Backup and revovery
increnet and full backup -- increment--> differnia plus cumulative
it will take the backup from the last backup .
what will happen if we enable block change trakcing in daabas?e how it effect incremental backup
if bck is enabled then it will look for the datafile which is keeping the change happeing in databse. it will only increment backup those change and
it is very fast ..not impcating performca
obsolete and expired
-- if we putting the renenation period of 3 days .. then over 3 days backup will be considered as expired backup. thats expiry.
show all
backup rentnetion policy
--
differnce in logical and physical backup .? Is it mandatory to shutdown the database physical backup
db will be up and runing - logical
What is there in dump file 9expdp)
strings myfile.dmp|more
While restarting db realizeed p file is not availble ?
we can manually careain using alert log file
ASM And RAC
-- local listener and remote listner.
A local listener is a listener on the same host
as the database instance.
A remote listener is a listener on a different (remote) host from the database instance.
Typically - remote listeners are used in a RAC configuration. You can set up a single listener (or a set of listeners) on a set of machines to redirect connections to the proper database instance. For example, you could set up machine A with a listener - on machines B and C are your database instances for the single database X. You could connect to the listener on A and ask it to connect you to database X (service X really). The listener would perhaps be configured to do load balancing and would then see which of B and C are 'least loaded' and redirect you to that database instance
A remote listener is a listener on a different (remote) host from the database instance.
Typically - remote listeners are used in a RAC configuration. You can set up a single listener (or a set of listeners) on a set of machines to redirect connections to the proper database instance. For example, you could set up machine A with a listener - on machines B and C are your database instances for the single database X. You could connect to the listener on A and ask it to connect you to database X (service X really). The listener would perhaps be configured to do load balancing and would then see which of B and C are 'least loaded' and redirect you to that database instance
-- Single point of failure
if you
only have a local listener with RAC and that node dies - you would have what we
call a single point of failure, yes.
SCAN:
Single Client Access Name -- Allowing client to access whole cluster/grid
through one name, rather specifying all VIP addresses individually
SCAN Requirement: To be able to get SCAN Functionality, We need some service through which one name is resolved to multiple IPs/hosts. This is what we often call "DNS", Domain name service or if you don't have DNS in your environment then you can use Oracle's "GNS (Grid Naming Service)"
SCAN Requirement: To be able to get SCAN Functionality, We need some service through which one name is resolved to multiple IPs/hosts. This is what we often call "DNS", Domain name service or if you don't have DNS in your environment then you can use Oracle's "GNS (Grid Naming Service)"
Que#1:
Will three SCAN listeners have same same port to listen?
ANS:
Yes all three Scan Listeners will have same port and you can choose the port
and the port will be different than the port used for VIP listeners. :-) Can't
Share the port# which i used in my production.
Que#2:
Three SCAN listeners take the request parallel?
ANS:
Yes three Scan listeners will run in parallel and accept requests, DNS will
route request to one of the SCAN Listener.
Que#3:
So from 11.2 onwards we are not using VIP?
ANS:
VIPs still exists on each node, But we dont use VIPs in our client connection
string instead we we use one SCAN-Name.
Que#1:
It does seem VIPs are redudent in that case?
ANS:
There is difference between VIP and SCAN Listener, Let me try to explain and
hope the example at the end will help to understand in more detail, First
difference is VIP is always bound to a node and each node will specifically
have one VIP Listener Where as Grid Services will start the SCAN Listener on
any node e.g if you have three SCAN Listener and 2/3 RAC Nodes then it may be
the case that 2 SCAN Listeners in one node and 1 SCAN Listerner on second node
where as third node doesn't have any SCAN Listerner. For VIPs if a certain node
is offline then that VIP will not be available in Cluster where as if certain
node is offline then Cluster will relocate the SCAN to other surviving nodes.
Advantage
of SCAN: Previously we specified all the VIPs in our client connections but now
you need to specify only one "SCAN Name" in your client address. If
you Add/Remove Nodes in your RAC then you dont need to update your client
connection strings, The only change will be done in DNS.
==================================
Row chaining occurs when the row is too large
to fit into any one block and hence it is split and stored in different blocks.
This is done only after oracle tries to fit the row into any one block in the
particular segment and failing which the row is split and stored. Each piece of
the row will have a head and a tail like a linked list that has the address of
the block of its preceding piece and succeeding piece.
or example, if you use a 4KB blocksize for your database, and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store the row in pieces
or example, if you use a 4KB blocksize for your database, and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store the row in pieces
Now row migration is different in the aspect that it is possible to fit the row into any one block in the segment that has enough space to accomodate the row. This will have only a pointer in the original block of the row that points to its new block address.
No comments:
Post a Comment